Error in subtotals

Question & Answer
, , ,
1

Hi
I want to know why all my subtotals got me the same.

image
image155×467 3.6 KB

2

Hi @Geral,
Looking at your screenshot, it looks like you may have it set to ‘Min’ as 5.65% looks to be the smallest percentage in each grouping.
You can change this from the field menu option:

image
image403×359 20.6 KB

Let me know if this helps work for your case or if you have any additional questions.

Thank you!

3

@Brett ok there is any way to put 100% on the top?? whithout afect the subresults

image
image112×132 3.17 KB

4

Hi @Geral,
Unfortunately, there’s currently no function to sort by the subtotals. The sorting will only affect the ‘lowest’ partition field when working with pivot tables.

I can mark this as a feature request as this is not the first time I’ve seen this asked for.

Let me know if you have any additional questions!

1 Like
5

hi @Geral hope this message finds you well!

image

I understand that you want something similar as the image above, right?
For me it’s get like this when I create a calculated field using the function percentOfTotal() for made this.
so the final question is: how did you make the field that are in Values?
let me know if this help you

7

Hi ok, @lary_andr
I just want to calculate the weight up to the top based on the total data, I attach the image of how to calculate that weight

image
image286×199 8.42 KB

image
image994×109 2.02 KB

image
image112×145 3.51 KB

8

@Geral ,
Now I understand!!.

Breaking down each part of the formula:

  1. Numerator:

    sumOver({GMS 2025}, [channel, marketplace], PRE_AGG)
    • This part is summing the values of {GMS 2025} for each combination of channel and marketplace before the final aggregation, due to the use of PRE_AGG.

  2. Denominator:

    sumOver({GMS 2025}, [marketplace], PRE_AGG)
    • This part is summing the values of {GMS 2025} for each marketplace before the final aggregation, again due to the use of PRE_AGG.

The reason why the total for the category might not be 100% is because the calculation is being done over two variables in the numerator (channel and marketplace), while the denominator is considering only one variable (marketplace). This means that the sum in the numerator is being divided by a sum that is less granular (only by marketplace), which can lead to ratios that do not add up to 100% when aggregated at a higher level.

If the goal is for the sum of the ratios for each channel within a marketplace to add up to 100%, you would need to ensure that the denominator is consistent with the level of detail in the numerator. One approach would be to adjust the denominator to reflect the same granularity as the numerator, or adjust the aggregation logic to ensure the ratios are calculated correctly.

Could you please flag my answer as a solution if it’s help you? It helps me grow in the community. Thank you!

9

@lary_andr Hello, I understand, but my goal is for each channel to have its weight and that in the subtotal (up to the top) of 100% because my logic is that now the total should be divided by the total, thus giving 100 (the 46 divided by 46, but the others divided by 46)

10

but if you see the syntax of percentOfTotal()

percentOfTotal(aggregated measure, [partition by attributes] )

you can put both attributes [channel, marketplace] in your case and it’s gonna work

1 Like