Cumulative Percent based on Product & TOPN without dates

DAX DAX Calculations
1

How to calculate cumulative percentage based on Product wise including TOPN.

In my data model have no dates.

It would be great if you could resolve in attached file.

Sample Data Cumulative Percent.pbix (31.0 KB)

2

Hello @khurram

Thanks for posting your query in the Forum,

Here is the solution for your query and the PBIX file

image

DAR Measure 1

Cumulative % =

VAR _Rank = RANKX(ALL(Products[Products]), [Percentage], , DESC)

VAR _Cumulative = IF(HASONEFILTER(Products[Products]),

                CALCULATE([Percentage],

               FILTER(ALL(Products[Products]),

               _Rank >= RANKX(ALL(Products[Products]), [Percentage], , DESC))),

               BLANK())

Return

_Cumulative

DAX Measure 2

Cumulative TopN =

VAR _Rank = IF(ISBLANK([TopN Total]), BLANK(), RANKX(ALL(Products[Products]), [TopN Total], , DESC))

VAR _CumutiveTopnN = IF(HASONEFILTER(Products[Products]),

                 CALCULATE(

                           [TopN Total],

                           FILTER(ALL(Products[Products]),

                           _Rank >=

                           IF(ISBLANK([TopN Total]), BLANK(),

                           RANKX(ALL(Products[Products]), [TopN Total], , DESC)))),

                 BLANK())

Return

_CumutiveTopnN

Sample Data Cumulative Percent_dharma.pbix (31.7 KB)

3

Hello Dharma,
Thanks you so much I was looking the same solution as you did.
Could you please fix the total? It’s showing blank.

Thanks & Regards,

4

Hello @khurram

You can remove the Hasonefilter condition from the measure, I have written that to make total blank…

Also please mark my reply as solution it will be great help

Thanks,
Dharma

1 Like
5

Hi @khurram,

Did the response above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need? If it did, please mark the solution that helped you.

Thank you!

6

Hi @khurram

Due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!