Cumulative Total for Pareto Using RANKX

[danextian]

Hi All,

I need help in creating a measure to calculate cumulative total. I was able to do it in my other report and I'm trying to replicte it. Not sure what is wrong but the cumulative total is showing the total for all instead.

Here's the dataset i used for a table called Pareto (the dataset is exported from a calculated table using ADDCOLUMNS and SUMMARIZE)

https://www.dropbox.com/s/ze7ac9001jsm1h1/Pareto.xlsx?dl=0

I created a calculated column for the rank

Questions Ranking = 
RANKX ( Pareto, Pareto[Occurence] )

And this measure for cumulative ranking

Cumulative Callout Occurence = 
CALCULATE (
    sum(Pareto[Occurence]),
    FILTER (
        ALLSELECTED ( Pareto ),
        Pareto[Questions Ranking]
            <= MAX ( Pareto[Questions Ranking] )
    )
)

It supposed to show cumulative total based on  ranking  but the measure is showing overall total. 

The first table in the image below is by Questions, the  second one is by rank

Thanks in advance for the help!

[v-sihou-msft · Accepted solution]

@danextian

In this scenario, you want to analyze on Question Level, when you calculate "cumulative total based on  ranking", this "ranking" should be group on Question Level. So your ranking column should be like: 

Questions Ranking = 
RANKX ( ALL(Pareto[Questions]), CALCULATE(SUM(Pareto[Occurence]),ALLEXCEPT(Pareto,Pareto[Questions])),,DESC,Dense)

Then you cumulative total measure should work properly. 

Regards,