Forum Discussion

o59393's avatar
o59393
Post 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 = [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.

 

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.

     

14 Replies

    • o59393's avatar
      o59393
      Post 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
        Super User
        2 years ago

        Hi,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

     

    • o59393's avatar
      o59393
      Post Prodigy
      2 years ago

      Hi Anonymous 

       

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


      Thanks.

  • AhmadBakr's avatar
    AhmadBakr
    Helper IV
    2 years ago

    To get the Pareto working correctly:
    either:
    1- In power Query, sort your percentage values descendingly, then insert an index column, then in PBI you can use the index in the measure to sum the records <= the current index, using sumx for iteration

    2- Or you can create a rank measure then use it for the conditional sumx