Forum Discussion

adibhaan98's avatar
adibhaan98
Frequent Visitor
2 years ago

Pareto Ranking is wrong!

Hello! I made a pareto to show the distribution of spend for suppliers but when I filter to "Top 20 Suppliers, the pareto and ranking are wrong. How do I fix this?  Without Filter:     With F...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi adibhaan98 ,

Please have a try.

Rank = 
IF(
    HASONEVALUE('Oracle Data'[Supplier]),
    RANKX(
        ALLSELECTED('Oracle Data'[Supplier]),
        [Total Spend]
    ),
    BLANK()
)
Cumulative Invoices = 
IF(
    HASONEVALUE('Oracle Data'[Supplier]),
    SUMX(
        TOPN(
            [Rank],
            CALCULATETABLE(
                VALUES('Oracle Data'[Supplier]),
                ALLSELECTED('Oracle Data')
            ),
            [Total Spend]
        ),
        [Total Spend]
    ),
    BLANK()
)
Cumulative Percentage = 
[Cumulative Invoices] /
CALCULATE(
    [Total Spend],
    ALLSELECTED('Oracle Data')
)

 

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

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

 

 

 

 

  • adibhaan98's avatar
    adibhaan98
    Frequent Visitor
    2 years ago

    This is the same formula I used.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi adibhaan98 ,

      I have changed the formula.

      Change All to Allselected.

      Rank = 
IF(
    HASONEVALUE('Oracle Data'[Supplier]),
    RANKX(
        ALLSELECTED('Oracle Data'[Supplier]),
        [Total Spend]
    ),
    BLANK()
)

       

      Cumulative Invoices = 
IF(
    HASONEVALUE('Oracle Data'[Supplier]),
    SUMX(
        TOPN(
            [Rank],
            CALCULATETABLE(
                VALUES('Oracle Data'[Supplier]),
                ALLSELECTED('Oracle Data')
            ),
            [Total Spend]
        ),
        [Total Spend]
    ),
    BLANK()
)


      Based on the formulas you've provided, it seems we need to adjust the context in which the rankings are being calculated when the filter is applied. The issue may be arising because the function is removing all the filters when calculating the rank and cumulative values, which is why your pareto distribution is not accurate after filtering.

       

      How to Get Your Question Answered Quickly 

       

      If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

       

      Best Regards
      Community Support Team _ Rongtie

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

      • adibhaan98's avatar
        adibhaan98
        Frequent Visitor
        2 years ago

        I tried it and it's  still not working