Pareto Ranking is wrong!

[adibhaan98]

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 Filter: 

 

 

Here are the formulas I've been using: 

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

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

Cumulative Percentage = [Cumulative Invoices]/CALCULATE([Total Spend],ALL('Oracle Data'[Supplier]))

Thanks! 

[adibhaan98]

 This is what the ranking and percentages look like

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[adibhaan98]

Hi, how would i do that?

[Ashish_Mathur]

Google Drive link.

[Anonymous]

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]

This is the same formula I used.

[Anonymous]

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]

I tried it and it's  still not working