Forum Discussion

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

Pareto not working

Hi all

 

I have a Pareto formula that has some issues when the % of dedicated time column has repeated values:

 

 

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.

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, o59393 
    I am sorry to reply to you so late.

    Create a new Index named Secondary Index in PowerQuery as a unique identifier and as a secondary sort.

     

    Close and Apply. Then create a new measure and try the following DAX expression:

     

    Index Test = 
 ROWNUMBER (
     ALLSELECTED ( 
        Template[Facet],
        Template[Tier 1: Process Facet],
        Template[Tier 2: Activity Facet]
      ),
     ORDERBY ( [Non Duplicate Hours Process/Activity 3],DESC, MAX('Template'[Secondary Index]),ASC )
 )

     

     

    Here is my Preview:

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, o59393 

    Perhaps you might consider adding a minor sort sequence to the RANK and RANKX functions to handle duplicate values in the "% of dedicated time" column. This secondary sort should be a unique identifier for each row. 

    You can refer to the following DAX:

    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, [UniqueIdentifier], ASC )
 )

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], [UniqueIdentifier], DESC, Skip),
                    BLANK())

    Replace [UniqueIdentifier] with the actual unique identifier in your data. This should help to distinguish between them.

    If you can, please provide more details with your desired output and pbix file without privacy information (or some sample data)

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, o59393 
        I am sorry to reply to you so late.

        Create a new Index named Secondary Index in PowerQuery as a unique identifier and as a secondary sort.

         

        Close and Apply. Then create a new measure and try the following DAX expression:

         

        Index Test = 
 ROWNUMBER (
     ALLSELECTED ( 
        Template[Facet],
        Template[Tier 1: Process Facet],
        Template[Tier 2: Activity Facet]
      ),
     ORDERBY ( [Non Duplicate Hours Process/Activity 3],DESC, MAX('Template'[Secondary Index]),ASC )
 )

         

         

        Here is my Preview:

         

        How to Get Your Question Answered Quickly 

        Best Regards

        Yongkang Hua

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