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

      You are welcome.

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

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

      Hi,

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