Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a measure to calculate a dynamic percentage "Ranking %" system for "Tech Rating", shown in the pic below. The code for this measure is also below. I want to have a slicer (that does not impact the table below it) that allows you to filter on a specific client and show the "Ranking %" beside it.
Ranking % = VAR _rnk = MINX ( FILTER ( SELECTCOLUMNS ( ALLSELECTED ( 'Table1' ), "Index", 'Table1'[School Systems.Campusname], "Rank", RANKX ( ALLSELECTED ( 'Table1' ), 'Table1'[Tech Rating],, DESC, SKIP ) ), [Index] = MAX ( 'Table1'[School Systems.Campusname] ) ), [Rank] ) VAR _all = CALCULATE ( COUNTROWS ( VALUES ( 'Table1'[School Systems.Campusname] ) ), ALLSELECTED ( 'Table1' ) ) RETURN IF ( _all = 1, 1, DIVIDE ( _rnk - 1, _all - 1) )
Hi @Anonymous
Is this what you want?
If so, please create measures
based = SUM('Table'[Rating]) rank measure = RANKX(ALLSELECTED('Table'),[based],,DESC,Dense) all count = CALCULATE(DISTINCTCOUNT('Table'[name]), ALLSELECTED('Table'),TREATAS('not impact table','Table'[name])) Measure 4 = IF([all count]=1,1,DIVIDE([rank measure]-1,[all count]-1))
Or if you want the result below
Measure 5 = VAR c = CALCULATE ( DISTINCTCOUNT ( 'not impact table'[name] ), ALLSELECTED ( 'not impact table' ) ) RETURN IF ( c = 1 && SELECTEDVALUE ( 'not impact table'[name] ) = MAX ( 'Table'[name] ), 1, DIVIDE ( [rank measure] - 1, CALCULATE ( DISTINCTCOUNT ( 'not impact table'[name] ), ALL ( 'not impact table' ) ) - c ) )
Hi @Anonymous
Hi @Anonymous
I'm waiting the percentile to display as a card style visual. I've already created the measures I need to calculate, but I need it, for the specific client, to be the only number that's visualized...
Hi @Anonymous
Is my understanding of your measure "Ranking %" correct?
name | Rating | rank | rank rate |
client1 | 4.1 | 7 | "(1-1)/(7-1)" |
client2 | 4.9 | 1 | "(1-1)/(7-1)" |
client3 | 4.3 | 5 | "(5-1)/(7-1)" |
client4 | 4.4 | 4 | "(4-1)/(7-1)" |
client5 | 4.7 | 2 | "(2-1)/(7-1)" |
client6 | 4.2 | 6 | "(6-1)/(7-1)" |
client7 | 4.5 | 3 | "(3-1)/(7-1)" |
If it is corretct, when select "client 2" from slicer, you want to card to show value 0.667(= "(5-1)/(7-1)"=4/6),
Right?
if you use the Card visualization and load the ranking% into it, and then have your slicer next to it, you'd get an updated value in the Card with that client's % only...right?
When filtered onto a single client, it defaults to 100%.
so I built a simple table:
Column1Column2
a | 5.00% |
b | 23.00% |
c | 15.00% |
d | 2.40% |
e | 75.00% |
f | 100.00% |
and then loaded a slicer and a card. It works fine.
Here's what to look for: in the visualizations column, for the Field data, pull down "column1" and you'll see a sub menu.
Sum is fine....or choose other as you see fit. Notice also the "Show Value as", and select that, and it should say % of total. Switch that to no calculation.
Then you'll get the individual % you want when you click on a single section in the slicer.
Thanks for the in depth responce. Unfortunately, I'm working with a measure, not a column, so this methodology doesn't work either.
well this is effectively what I was describing...I would still do it in the table...here's another answer that is getting at my method:
The link you provided is more in line with what I'm trying to do! However, the list of variables in the slicer is only two items long, where mine is hunderds long... any idea about how to modify that method to account for many more variables in the slicer?
maybe I just don't understand, but if you build a calculated column vs. a measure, then you have the %s loaded in the table...then the slicer and card are straightforward. If you must do a measure, then it's ugly...why is a calculated column not ok?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |