Pareto not working

[o59393]

Hi all

I have the following table:

The pareto should sum the column % of dedicated time to look cumulative like this:

The measure used is:

Pareto = 

VAR demand = [Non Duplicate Hours Process/Activity 3]

RETURN

SUMX(
FILTER(
SUMMARIZE(ALLSELECTED(Template),'Template'[Merged.4],Template[Facet],
"Percentage", [Non Duplicate Hours Process/Activity 3]),
[Percentage] >= demand),
[Percentage]
)
        

Where Merged.4 is the combination of Function & Tier 1: Process & Tier 2: Activity

How can I get the percentages correct?

Thanks.

[v-kongfanf-msft · Accepted solution]

Hi @o59393 ,

You should create index column in power query. Then try formula like below:

pareto =
VAR cur_ = 'Table'[Name]
VAR cur_index = 'Table'[Index]
RETURN
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Name] = cur_ && 'Table'[Index] <= cur_index ),
        [TotalPercentage]
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[v-kongfanf-msft · Accepted solution]

Hi @o59393 ,

You should create index column in power query. Then try formula like below:

pareto =
VAR cur_ = 'Table'[Name]
VAR cur_index = 'Table'[Index]
RETURN
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[Name] = cur_ && 'Table'[Index] <= cur_index ),
        [TotalPercentage]
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.