Forum Discussion
Cumulative sum on chart and table
Hello there!
I'm facing what it looks a strange case: I have a cumulative sum that works perfectly within a chart (line in screenshot here below), but it reports wrong values if inserted in a table or matrix:
Correct visualization
Wrong visualization (I would expect that in the last column cumulative sum appears)
The measure I'm using in both visuals is this:
Cumulative Gross =
CALCULATE (
[Gross],
FILTER (
ALL ( Calendario ),
Calendario[Date] <= MAX ( Calendario[Date] )
)
)
Have you any ideas how to figure out?
Thank you very much,
bye!
Hi Brancaleone ,
Please update the formula of measure as below and check if it can return the expected result:
Cumulative Gross2 = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[SAL] <= MAX ( 'Table'[SAL] )&& 'Table'[ABCKO field] <= MAX ( 'Table'[ABCKO field] ) ), [Gross] )Best Regards
Hey Brancaleone ,
Apologies seems like notifications for this thread were turned off thanks for stepping in Anonymous
I'll make some assumptions that the fact table has a date column, since it is linked to the Calendario dimension. You could rewrite your measure to handle both cumulative situations
Cumulative Gross = CALCULATE ( [Gross], -- Handling the date dimension ALL( Calendario ), Calendario[Date] <= MAX( Calendario[Date] ), -- Handling the fact ALL( 'fact'[Date] ), 'fact'[Date] <= MAX( 'fact'[Date] ) )As you can see we are veering away from best practice here, as you rightly mentioned. It would be better to have the relevant labels in the date dimension itself and your original code should work. However, I understand it is sometimes not a possibility to remodel depending on your data access/security etc.
The code above should handle any future labels you use from your fact table if you decide to drill down further.
Hope it helps,
Kris
5 Replies
- kriscoupe
Solution Supplier
Hey Brancaleone ,
Just to check, does the SAL column you are displaying in the matrix exist in the Calendario dimension table or a different table. If you can, it might be worth sharing the model view and the columns you used on the axis for both the line chart and the table (as they look different).
Cheers,
Kris
kriscoupe Thank you for your contribution to this thread. Your insights and suggestions are valuable to the community.
Hi Brancaleone ,
As kriscoupe said, the fields which applied on the column chart and table visual are different. The x-axis on the column chart is the field Calendario[Date]. However, the field which applied on the table visual is [SAL]. So you can consider to create a measure as below to get the culmulative values when it is in the table visual:
Cumulative Gross2 = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[SAL] <= MAX ( 'Table'[SAL] ) ), [Gross] )Best Regards
Thank you kriscoupe and Anonymous for your answers.
kriscoupe: the SAL column in the matrix does not exist in the Calendario dimension table, it is on the same table with values (I know, it is not the best practice).As v-yiruan-msft says, on the chart we have Calendario dates on axis x (dimension table), and of course values on axis y (fact table): Calendario filters the fact table.
Anonymous: your measure fixes pretty well:
Correct visualization
Unfortunately (my fault, I did not explain this before) it does not report correct data when we drill it:
Wrong visualization in Cumulative Gross details
Also A, B, C, etc... values are in the same fact table. How can I fix it?
- kriscoupe
Hey Brancaleone ,
Apologies seems like notifications for this thread were turned off thanks for stepping in Anonymous
I'll make some assumptions that the fact table has a date column, since it is linked to the Calendario dimension. You could rewrite your measure to handle both cumulative situations
Cumulative Gross = CALCULATE ( [Gross], -- Handling the date dimension ALL( Calendario ), Calendario[Date] <= MAX( Calendario[Date] ), -- Handling the fact ALL( 'fact'[Date] ), 'fact'[Date] <= MAX( 'fact'[Date] ) )As you can see we are veering away from best practice here, as you rightly mentioned. It would be better to have the relevant labels in the date dimension itself and your original code should work. However, I understand it is sometimes not a possibility to remodel depending on your data access/security etc.
The code above should handle any future labels you use from your fact table if you decide to drill down further.
Hope it helps,
Kris
Hi Brancaleone ,
Please update the formula of measure as below and check if it can return the expected result:
Cumulative Gross2 = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[SAL] <= MAX ( 'Table'[SAL] )&& 'Table'[ABCKO field] <= MAX ( 'Table'[ABCKO field] ) ), [Gross] )Best Regards
