Error in measure - cumulative measure used for Pareto chart

[Henrik_PBI]

Hello,

I have created a measure (with help from users on this forum) which I use in a pareto chart. The measure shows cumulative amount (the value of the row, added with the summarized values for the previous rows).

But I realize that I have an error in my measure, which I need help to solve. It seems that if two rows have the same value, the two rows will be added together, and the cumulative measure will show the same sum on both rows.

In the image below you can se that the cumulativ measure (column 3) shows 1008,63+(8+8) =1104,63 for both rows marked in red.
What I want it to add every single row, so that the cumulative measure should show 1108,63+8= 1096,53 for the first row and 1096,63+8=1104,63 for the second

My measure looks like this:

Cumulative = 
VAR ReasonRank = 
    RANKX(
        ALL(events[Related_reason]);
        [Total_amount];;
        DESC
    )
VAR Cumulative = 
    CALCULATE(
        [Total_amount];
        FILTER(
            ALL(events[Related_reason]);
            ReasonRank >= RANKX(
                            ALL(events[Related_reason]);
                            [Total_amount];;
                            DESC;
                            Dense)
            )
        )
RETURN 
      Cumulative

I guess this happens because two rows with similar values will get the same rank - but can anyone help me fix the measure so that this doesn't happen?

[Henrik_PBI · Accepted solution]

I think I solved this, inspired by the suggestion in @Jihwan_Kim 's post.

The problem was that I got duplicated rankings when the values on multiple rows where equal. I solved this by adding a index column and included this in the measure.

Here is the updated measure:

Cumulative = 
VAR ReasonRank = 
    RANKX(
        ALL(events[Related_reason]);
        [Total_amount]+([Index]/100000);;
        DESC;Skip
    )
VAR Cumulative = 
    CALCULATE(
        [Total_amount];
        FILTER(
            ALL(events[Related_reason]);
            ReasonRank >= RANKX(
                            ALL(events[Related_reason]);
                            [Total_amount]+([Index]/100000);;
                            DESC;
                            Dense)
            )
        )
RETURN 
      Cumulative
    

[Jihwan_Kim]

Hi, @Henrik_PBI 

I am not sure how your data model and table structure look like, but in my case, if I faced a similar situation, I consider creating the additional Total Amount Measure only for the ranking purpose.

For instance, in the sample pbix file's link down below, which I created, there are several same totals. In this case, I use the index number / 10000 and add this to the Value total, then create the ranking measure. In this case there would be no same ranking.

You can also use another measure or another column to create this.

https://www.dropbox.com/s/0osdqkrw5xxq59a/henrik.pbix?dl=0 

Value Total for ranking purpose =

SUMX('Table', 'Table'[Value] + DIVIDE('Table'[Index], 10000))

Value ranking =

RANKX(ALL('Table'[Item]), [Value Total for ranking purpose],,DESC)

Value Cumulate =

VAR ranking = [Value ranking]

RETURN

CALCULATE (

SUM ( 'Table'[Value] ),

FILTER ( ALL ( 'Table'[Item] ), [Value ranking] <= ranking )

)