Calculate Percentile of a Data-Point

[Anonymous]

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) )

[v-juanli-msft]

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
        )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[Anonymous]

The column you created for the %ranking does the same thing, but it is not dynamic, which is the reason I need the measure that I referenced above. If I was to filter out a client, I would need denominator to change to 5, not 6.

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

Best Regards

Maggie

[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...

[v-juanli-msft]

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?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gckcmc]

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?

[Anonymous]

When filtered onto a single client, it defaults to 100%.

[gckcmc]

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.

[Anonymous]

Thanks for the in depth responce. Unfortunately, I'm working with a measure, not a column, so this methodology doesn't work either.

[gckcmc]

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:

https://community.powerbi.com/t5/Desktop/Dynamic-Measure-Values-Based-on-Slicer-Selection-with-Optio...

[Anonymous]

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?

[gckcmc]

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?

[Anonymous]

I do need it to be a measure, unfortunately. This is a dynamic variable that I'm trying to create as a function of the rest of the dashboard.