Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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
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
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |