Ranking Error - showing 1

[ViralPatel212]

Wondering if you could help. 

Second post but getting no help?

I am trying to create a simple summarized table where it shows where Barclays sits for each category.

in my main table the ranking works using this formula. I have filtered the category to NNIP and it shows that Barclays is ranked 6 based of Vol. (MM).

Ranking: IF( [Ranking Size (Vol)] = blank(), BLANK(), RANKX(ALLSELECTED('Dealer Ranking'[Counter Party]),[Ranking Size (Vol)],,DESC))

However when i table showing jus category and the rank it doesn't work.

In this on the allselected didnt work so i tried:  

Dealer Ranking 4 = RANKX(ALL('Dealer Ranking'[Counter Party]),[Ranking Size (Vol)],,DESC) 

as you can see i have put a visual filter to show Barclays but im getting 7 rather than 6?

Hope this make sense.

thanks

viral

[v-kaiyue-msft]

Hi @ViralPatel212 ,

Thanks for the reply @MNedix , I have some suggestions here, I hope they can be helpful to you.

The ALL function ignores any incoming filter, whether the filter is from the same visual or external. However, the ALLSelected function only ignores filters from the same visual and accepts filters from outside. When you apply a visual filter to only show Barclays, it is important to ensure that the ranking calculation still takes into account the background of other brokers to accurately reflect Barclays' position among those brokers. If the filter context is not passed to the function correctly, it may calculate the ranking in a different context, leading to unexpected results. For more details, please refer to the link: Power BI DAX: ALL vs. ALLSelected - RADACAD.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ViralPatel212]

Hi @v-kaiyue-msft 

Thanks for the informative answer. I was struggling to understand the difference between All and AllSelected but its does make sense.

The current period in the example, i don't think it impacts on the results, however if i replaced the all function to Allselected function i am getting a the Value as 1? 

Do i need to add the Client column into the measure?

I am not sure why i am doing wrong here

[v-kaiyue-msft]

Hi @ViralPatel212 ,

I see multiple slicers in your screenshot, and I don't know the relationship between your tables, so I can't determine what affects the calculation results of measure. You use the allselected function and the result is displayed as 1, most likely due to the effect of the external filter.

If you can provide more details, I can better help you solve your problem.

Best Regards,

Clara Gong

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

[ViralPatel212]

Hi @v-kaiyue-msft 

First of all, i just want to say thank you so much for responding! 

I can created a dummy file with my current problem. 

Within the pbix file i have added a "Desired outcome table" to what i am trying to acheive and also a incorrect values as a columm (how the current rankall function is working)

You can access the file below:

Ranking Error 

Thanks once again

[v-kaiyue-msft]

Hi @ViralPatel212 ,

Your ranking results are not as expected because of the MEASURE that sums over SIZE, which sums over different results in different contexts, and therefore gives unexpected results. You can modify the expression by following these steps.
1. Create the measure and re-sum the size.

Measure =
VAR _currentCTP = MAX('Sheet1 (2)'[Counter Party])
VAR _currentBuySide = MAX('Sheet1 (2)'[buy_side])
RETURN
CALCULATE(SUM('Sheet1 (2)'[Size]),FILTER(ALL('Sheet1 (2)'),'Sheet1 (2)'[Counter Party]=_currentCTP&&'Sheet1 (2)'[buy_side]=_ currentBuySide))

2. create measure and sort the results.

Measure 2 =
IF('Sheet1 (2)'[Measure]<>BLANK(),RANKX(ALL('Sheet1 (2)'[Counter Party]),[Measure],,DESC)))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ViralPatel212]

Hi @v-kaiyue-msft 

Thank you for that! i do understand the logic, however i did forget to add slicer into the example. For example I added a currency slicer onto the report and filtered to ALL (EUR) and the ranking (Measure 2) does not work. 

e.g. Client 1 Measure 2 should show 3 but its showing 6

Ultimatly there would be more than 1 slicers on the page and depending on that it should rank it accordingly 

Ranking Total 

[v-kaiyue-msft]

Hi @ViralPatel212 

I've complete the logical and lmitation of the Dax, and what you should do is to make the two tables' filter the same, different filter can give the different outcomes of the measure, here for your reference:

Row Context and Filter Context in DAX - SQLBI

Here's the measure:

Measure = 
VAR _currentCTP =
    SELECTEDVALUE( 'Sheet1 (2)'[Counter Party] )
VAR _currentBuySide =
    SELECTEDVALUE( 'Sheet1 (2)'[buy_side] )
VAR _currentCurrency =
    MAX ( 'Sheet1 (2)'[Currency] )
RETURN
    IF (
        ISFILTERED ( 'Sheet1 (2)'[Currency] ),
        CALCULATE (
            SUM ( 'Sheet1 (2)'[Size] ),
            FILTER (
                ALL ( 'Sheet1 (2)' ),
                 'Sheet1 (2)'[Counter Party] = _currentCTP
                    && 'Sheet1 (2)'[buy_side] = _currentBuySide
                    && 'Sheet1 (2)'[Currency] = _currentCurrency
            )
        ),
        CALCULATE (
            SUM ( 'Sheet1 (2)'[Size] ),
            FILTER (
                ALL ( 'Sheet1 (2)' ),
                    'Sheet1 (2)'[Counter Party] = _currentCTP
                    && 'Sheet1 (2)'[buy_side] = _currentBuySide
            )
        )
    )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[ViralPatel212 · Accepted solution]

Was able to resolve this: 

Measure: 
var _rank = RANKX(ALL('Dealer Ranking'[Counter Party]),[Ranking Size (Vol)],,DESC)
VAR _tab =
    FILTER (
        ADDCOLUMNS (
            ALL( 'Dealer Ranking'[Counter Party] ),
            "@rank", [Dealer Ranking Number 2 test]
        ),
        [Counter Party] = "Barclays"
    )
VAR _rankbarclays =
    MAXX ( _tab, [@rank] )


RETURN

_rankbarclays

[ViralPatel212]

Hi @v-kaiyue-msft 

Ah, perfect that solution does work. However it seems that I will have to hard code each slicer as a variable and then add them to the first part of the IF statement.

Doing this could be timing consuming as my main report has got 20 filters? 

Is there any other way to incorporate this?

Thanks

[MNedix]

TBH, I can't see anything in those screenshots, they are too small.

[ViralPatel212]

Hey @MNedix 

i have made the screenshots bigger to read.