Bucketing Values + showing actuall values

[ViralPatel212]

Hey Team,

I have a interesting query which hopefully should test you abit!

I need to create a measure that does the following thing (This is my understanding of the issue, i could be wrong):

(Updated image in the example below)

1) IF the Ranking is 1 Dealer with the highest Volume - than show the value

2) IF  Dealer is "Barclays than show the value 

3) Look at who is above "Barclays" in this example Raking 7 - show that dealer value

4) Look at who is below "Barclaus" in this example Ranking 9 - show that dealer value

5) Else create a range of the volume i.e. 200-400,400-800, 800-1000, 1000-1250, 1250 -1500, 1500 +

Measures Used:

Ranking Measure	IF( [Ranking Size (Vol)] = blank(), BLANK(), RANKX(ALL('Dealer Ranking'[test 2],'Dealer Ranking'[test]),[Ranking Size (Vol)],,DESC,Dense))
Ranking Size (Vol)	VAR _currency = [DR Currency Count] VAR _volumeall=     CALCULATE(         [Ranking Size],         FILTER(         'Dealer Ranking',         'Dealer Ranking'[Type] = "Vol. (MM)")) VAR _volumeeur =   CALCULATE(         [Ranking Size (EUR)],         FILTER(         'Dealer Ranking',         'Dealer Ranking'[Type] = "Vol. (MM)")) RETURN SWITCH(     TRUE(),     HASONEFILTER('Dealer Ranking'[Currency]),     _volumeall,     _volumeeur )
Ranking Size	SUM('Dealer Ranking'[Size])
Ranking Size (EUR)	SUM('Dealer Ranking'[Size (EUR)] )

I hope this is doable!

Thank You

[ViralPatel212]

@v-yiruan-msft 

Thank you for that, jus thought if you could help me with another issue.

I am trying to create a table where it identifies where "Barclays" is being ranked against the clients.

Issue here is that 4 of the positioning is incorrect and im unclear why. Also i need the ranking to be able to dynamically change when a user selects a any of the filters.

I have amended the sample file and it can be accessed here: Sample file 

[v-yiruan-msft]

Hi @ViralPatel212 ,

Please create a measure as below and check if you can get the expected result...

MEASURcE =
VAR _selvol =
    SELECTEDVALUE ( 'Dealer Ranking'[Vol.(MM)] )
VAR _dealer =
    SELECTEDVALUE ( 'Dealer Ranking'[Dealers] )
VAR _range =
    SWITCH (
        TRUE (),
        _selvol >= 0
            && _selvol < 200, "0-200",
        _selvol >= 200
            && _selvol < 400, "200-400",
        _selvol >= 400
            && _selvol < 800, "400-800",
        _selvol >= 800
            && _selvol < 1000, "800-1000",
        _selvol >= 1000
            && _selvol < 1250, "1000-1250",
        _selvol >= 1250
            && _selvol < 1500, "1250-1500",
        _selvol >= 1500, "1500+"
    )
VAR _rankbarclays =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Dealer Ranking' ),
            'Dealer Ranking'[Dealers] = "Barclays"
        ),
        [Ranking]
    )
RETURN
    SWITCH (
        TRUE (),
        [Ranking] = 1
            || _dealer = "Barclays", _selvol,
        [Ranking] = _rankbarclays - 1
            || [Ranking] = _rankbarclays + 1, _dealer,
        _range
    )

If the above one can't help you figure out, please provide some raw data in your table 'Dealer Ranking' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[ViralPatel212]

Hey @v-yiruan-msft 

Thank you for that! i can see 80% of the measure is working. 

At the moment, the range is working and its identifying the top dealer volume and barclays volume. 

However the measure is idenitfying rank 2 "bank of america" which is incorrect and instead it should show rank 5 and rank 7 volume.

I have created a sample file that can be accessed here: Sample file 

Thanks once again

[v-yiruan-msft · Accepted solution]

Hi @ViralPatel212 ,

Please update the formula of measure [testing volume] as below, you can find the details in the attachment.

testing volume = 
VAR _selvol = [Ranking Size (Vol)]
VAR _dealer =
    SELECTEDVALUE ( 'Dealer Ranking'[Counter Party] )
VAR _range =
    SWITCH (
        TRUE (),
        _selvol >= 0
            && _selvol < 200, "0-200",
        _selvol >= 200
            && _selvol < 400, "200-400",
        _selvol >= 400
            && _selvol < 800, "400-800",
        _selvol >= 800
            && _selvol < 1000, "800-1000",
        _selvol >= 1000
            && _selvol < 1250, "1000-1250",
        _selvol >= 1250
            && _selvol < 1500, "1250-1500",
        _selvol >= 1500, "1500+"
    )
VAR _tab =
    FILTER (
        ADDCOLUMNS (
            ALL ( 'Dealer Ranking'[Counter Party] ),
            "@rank", [Dealer Ranking Number 2]
        ),
        [Counter Party] = "Barclays"
    )
VAR _rankbarclays =
    MAXX ( _tab, [@rank] )
RETURN
    SWITCH (
        TRUE (),
        [Dealer Ranking Number 2] = 1
            || _dealer = "Barclays", _selvol,
        [Dealer Ranking Number 2] = _rankbarclays - 1
            || [Dealer Ranking Number 2] = _rankbarclays + 1, _dealer,
        _range
    )

Best Regards