Forum Discussion

ViralPatel212's avatar
ViralPatel212
Icon for Resolver I rankResolver I
2 years ago
Solved

Ranking Error - showing 1

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 rank...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • ViralPatel212's avatar
    ViralPatel212
    2 years ago

    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's avatar
ViralPatel212
Icon for Resolver I rankResolver I
2 years ago

Hi Anonymous 

 

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

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    ViralPatel212
    Icon for Resolver I rankResolver I
    2 years ago

    Hi Anonymous 

     

    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 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        ViralPatel212
        Icon for Resolver I rankResolver I
        2 years ago

        Hi Anonymous 

         

        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