How to get the cumulative total of the total value in DAX

[Kalaivani]

Hi All,

Can anyone please let me know on how to calculate Cumulative total based on the Max value in DAX. 

Below is the same data. I want the cumulative sum to be (1167.78/12)*1,  (1167.78/12)*2,  (1167.78/12)*3 for Oct Nov and Dec.

Thanks so so much!!

[Irwan · Accepted solution]

hello @Kalaivani 

i see, please check if this accomodate your need.

- with ID

- Without ID

1. create a new measure with following DAX to measure month number

Month = MONTH(SELECTEDVALUE('Table'[Month-Year]))

2. create a new measure with following DAX to measure cumulative with ID. Basically, you need to rank 'Month-Year' to make sure Oct has 1, Nov has 2, and Dec has 3. After that, measure MAX of 'Med Bench' for each ID.

Cumulative =
var _Rank = 
RANKX(
    ALL('Table'),
    [Month],,
    ASC,
    Dense
)
var _ID = SELECTEDVALUE('Table'[ID])
var _Max = 
CALCULATE(
    MAX('Table'[Med Bench]),
    ALL('Table'),'Table'[ID]=_ID
)
Return
_Rank*_Max/12

3. create a new measure with followind DAX to measure cumulative without ID

Cumulative no ID = 
SUMX(
    ALLEXCEPT('Table','Table'[Month-Year]),
    [Cumulative]
)

i might be wrong but i think you need to make two measures since the cumulative with ID value depend on ID in table visual and cumulative without ID dont need ID.

Hope this will help.

Thank you.

[danextian · Accepted solution]

Hi @Kalaivani 

Assuming that you need to aggregate the sum of benchmark by month and year then pick the highest monthly value, try this:

Max Aggregated Value for all visible months =
VAR MaxValue =
    CALCULATE (
        MAXX (
            SUMMARIZECOLUMNS ( Dates[Month and Year], "@rev", [Total Revenue] ),
            [@rev]
        ),
        ALLSELECTED ( Dates )
    )
RETURN
    DIVIDE ( MaxValue, 12 ) * MAX ( Dates[Month Number] )

Please see the attached sample pbix.

[danextian · Accepted solution]

Hi @Kalaivani 

Assuming that you need to aggregate the sum of benchmark by month and year then pick the highest monthly value, try this:

Max Aggregated Value for all visible months =
VAR MaxValue =
    CALCULATE (
        MAXX (
            SUMMARIZECOLUMNS ( Dates[Month and Year], "@rev", [Total Revenue] ),
            [@rev]
        ),
        ALLSELECTED ( Dates )
    )
RETURN
    DIVIDE ( MaxValue, 12 ) * MAX ( Dates[Month Number] )

Please see the attached sample pbix.

[Kalaivani · Accepted solution]

Hi @danextian  Thanks so much. It is working.

[Irwan]

hello @Kalaivani 

i am not sure the goal here but please check if this accomodate your need.

create a new measure with following DAX:

Cumulative Sum =
var _Max = CALCULATE(SUM('Table'[Benchmark_Med]),ALL('Table'))
Return
DIVIDE(
    _Max/12,
    SELECTEDVALUE('Table'[Index])
)

Hope this will help.
Thank you.

[Kalaivani]

Helo @Irwan Thanks for the reply. What is index here? If I use the same logic in mine, I am retreiving 0 for each month.

Could you pleae share the PBX file with me? 

I have attached the snip of sample data with the output that has to be shown(Cumulative column). Thanks again!

[Irwan · Accepted solution]

hello @Kalaivani 

i see, please check if this accomodate your need.

- with ID

- Without ID

1. create a new measure with following DAX to measure month number

Month = MONTH(SELECTEDVALUE('Table'[Month-Year]))

2. create a new measure with following DAX to measure cumulative with ID. Basically, you need to rank 'Month-Year' to make sure Oct has 1, Nov has 2, and Dec has 3. After that, measure MAX of 'Med Bench' for each ID.

Cumulative =
var _Rank = 
RANKX(
    ALL('Table'),
    [Month],,
    ASC,
    Dense
)
var _ID = SELECTEDVALUE('Table'[ID])
var _Max = 
CALCULATE(
    MAX('Table'[Med Bench]),
    ALL('Table'),'Table'[ID]=_ID
)
Return
_Rank*_Max/12

3. create a new measure with followind DAX to measure cumulative without ID

Cumulative no ID = 
SUMX(
    ALLEXCEPT('Table','Table'[Month-Year]),
    [Cumulative]
)

i might be wrong but i think you need to make two measures since the cumulative with ID value depend on ID in table visual and cumulative without ID dont need ID.

Hope this will help.

Thank you.

[Kalaivani]

Hi @Irwan Thanks so much. It is working.

[Irwan]

hello @Kalaivani 

it just an index column from PQ since again i am not sure why it is divided by 3 in Dec-24 (i only did what you described).

i can not attach pbix at this moment, but it is only one measure with that DAX.

so in your sample, how to get cumulative value? for example, how to get 97.315 at ID1 on Oct-24?

Hope this will help.

Thank you.

[Kalaivani]

Hi @Irwan  Oh Okay. Thanks for the reply. I took the max value actually for ID 1 which is 1167 and then I need to get monthly value based on that which would be 1167/12 which is 97.315 and based on that, I calculated cumulative value for each month.

And what I should use instead of Index based on my data?

Sorry for the confusion!!

[ryan_mayu]

could you pls provide some sample data not the table visual? will you change filter the date? if you select only Nov 2024, will the (1824.65/12)*2 change to  (1824.65/12)*1?