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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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