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.

[o59393]

Hi @Ashish_Mathur 

Just one question, when I have %'s with identical values, the Pareto and the Index function I created are not calculating correctly:

For instance, all the 5.15% values have the same index value of 2 and the pareto column doesnt sum correctly.

The formulas are:

Index = 
 RANK (
     DENSE,
     ALLSELECTED ( 
        Template[Facet],
        Template[Function],
        Template[Tier 1: Process Facet],
        Template[Tier 2: Activity Facet]
      ),
     ORDERBY ( [Non Duplicate Hours Process/Activity 3], DESC )
 )

RANK = 

if(HASONEFILTER(
    
    'Template'[Facet]),
    
    calculate(
        
        RANKX(
            
            GENERATE(
                
                GENERATE(
                    
                    ALLSELECTED('Template'[Tier 1: Process Facet]),
                    
                    ALLSELECTED('Template'[Tier 2: Activity Facet])),
                    
                    ALLSELECTED('Template'[Facet])),
                    
                    [Non Duplicate Hours Process/Activity Numerator],,DESC)),
                    
                    BLANK())

Running % = 

DIVIDE([Running total],

CALCULATE([Non Duplicate Hours Process/Activity Numerator],

ALLEXCEPT(Template,Template[Function],Template[Area])))

Running total = 

SUMX(TOPN(
    
    [Rank],
    
    CALCULATETABLE(
        
        GENERATE(GENERATE(values(
            
            'Template'[Facet]),
            
            values(
                
                'Template'[Tier 1: Process Facet])),

                values('Template'[Tier 2: Activity Facet])),
                
                GENERATE(
                    GENERATE(ALLSELECTED(
                        
                        'Template'[Facet]),
                
                ALLSELECTED('Template'[Tier 1: Process Facet])),
                
                ALLSELECTED('Template'[Tier 2: Activity Facet]))),
                
                [Non Duplicate Hours Process/Activity Numerator]),
                
                [Non Duplicate Hours Process/Activity Numerator])

Any idea how to fix this?

Thanks.

[Ashish_Mathur]

Hi,

Share some dummy data to work with and show the expected result.

[o59393]

Hi @Ashish_Mathur 

Please see attached a pbix sample: https://drive.google.com/file/d/1rID6tbIhYqanx6gcQnDaLESnn3eZr3vQ/view?usp=sharing

You will see repeated index (8 and 10 for example) where they have the same % value 3.51% and 2.51% respectively. 

The desired outcome (in green columns) would be to have the Running % column making the cumulative sum in each row.:

Thanks.

[Ashish_Mathur]

Hi,

Modify your rank measure to

RANK(DENSE,SUMMARIZE(generate(generate(ALLSELECTED('Template'[Tier 1: Process Facet]),ALLSELECTED('Template'[Tier 2: Activity Facet])),ALLSELECTED('Template'[Facet])),Template[Facet],Template[Tier 1: Process Facet],Template[Tier 2: Activity Facet]),ORDERBY([Non Duplicate Hours Process/Activity Numerator],DESC,Template[Tier 1: Process Facet],ASC,Template[Tier 2: Activity Facet],ASC))

This will assign a different number to each row.  Now carefully review your others measures to get them working.

[o59393]

Hi @Ashish_Mathur 

I modified the rank but stills doesnt show the right value on the Running % column:

Also, how could I have the index formula display 1,2,3 without repeating values or blanks?

Thanks.

[Ashish_Mathur]

As i mentioned, i have got the correct rank for you (different numbers).  Review and revise the other measures yourself to get your desired result.

[o59393]

My bad @Ashish_Mathur 

Rank works perfect:

The only issue is with the Runnng %.

Using your formula I still not get the right cumulative sum:

Running % = 

DIVIDE([Running total],

CALCULATE([Non Duplicate Hours Process/Activity Numerator],

ALLEXCEPT(Template,Template[Function],Template[Area])))

Running total = 

SUMX(TOPN(
    
    [Rank],
    
    CALCULATETABLE(
        
        GENERATE(GENERATE(values(
            
            'Template'[Facet]),
            
            values(
                
                'Template'[Tier 1: Process Facet])),

                values('Template'[Tier 2: Activity Facet])),
                
                GENERATE(
                    GENERATE(ALLSELECTED(
                        
                        'Template'[Facet]),
                
                ALLSELECTED('Template'[Tier 1: Process Facet])),
                
                ALLSELECTED('Template'[Tier 2: Activity Facet]))),
                
                [Non Duplicate Hours Process/Activity Numerator]),
                
                [Non Duplicate Hours Process/Activity Numerator])

Any idea on why this happens?

Thanks.

[Ashish_Mathur]

PBI file attached.

Hope this helps.

[o59393]

@Ashish_Mathur perfect!

Thanks.

[Ashish_Mathur]

You are welcome.

[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.

[o59393]

Hi @v-kongfanf-msft 

I see you did a calculated column. How can it be done with a measure?

Thanks.