Cumulative total by ranking with measure column

[SYKim]

Hello all,

here I want to have cumulative total by ranking order (not date) but all the column is measure column.

My current formula and figure is as below.

Cumulative = SUMX(FILTER(ALLSELECTED(BOM),[Ranking]<=Maxx(BOM,[Ranking])),[Year 1])

All columns for 'Year1' and 'Ranking' is measure column.

But I want above be like below.

Could you please help to correct my formula?

[v-cgao-msft · Accepted solution]

Hi @SYKim ,

Please try this measure:

Cumulative = 
VAR CurrentRank = [Ranking]
RETURN
    IF (
        HASONEVALUE ( 'BOM'[CAT] ),
        SUMX (
            FILTER (
                ALLSELECTED ( 'BOM'[PNO(merged)], 'BOM'[CAT] ),
                [Ranking] <= CurrentRank
            ),
            [Year 1]
        ),
        SUMX ( ALLSELECTED ( 'BOM'[PNO(merged)], 'BOM'[CAT] ), [Year 1] )
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[thians]

hi, 
Similiar with that case. How to create a measure group based on cumulative percentages like the following example

so I can display visuals like the following

I tried using an if condition but it doesn't display the values ​​per group. Can you help to create a proper dax formula ?
thanks

[SYKim]

OMG! it works. Thanks!

[Ashish_Mathur]

Hi,

Does this measure work?

=SUMX(TOP([Ranking],CALCULATETABLE(VALUES(BOM[PNO Merged]), ALL(BOM[PNO Merged])),[Year 1]),[Year 1])

[SYKim]

Hi,

no it does not work. I got below message.

[Ashish_Mathur]

Share the download link of the PBI file.