Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

Pareto not working

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 = [...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

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's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
2 years ago

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.