Cumulative sum on chart and table

[Brancaleone]

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!

[Anonymous · Accepted solution]

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

[Brancaleone]

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 · Accepted solution]

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

[Anonymous · Accepted solution]

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

[Anonymous]

@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

[kriscoupe]

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