Forum Discussion

Brancaleone's avatar
Brancaleone
Frequent Visitor
2 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

  • kriscoupe's avatar
    kriscoupe
    2 years ago

    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

Brancaleone's avatar
Brancaleone
Frequent Visitor
2 years ago

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's avatar
kriscoupe
Icon for Solution Supplier rankSolution Supplier
2 years ago

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