Display Percentiles

[sanchid]

Hi Community, 

I have a unique problem where I need to display scores ranging from 0 to 5 which can dynamically change based on filters applied. Below is the snapshot of my source table. Based on the Points column, I need to display a relative score on a PowerBI table, which will range from 0 to 5. But when choosing/selecting any filters, the range has to relatively display scores for the filtered accounts. Kindly let me know if it's confusing or needs more detailing.

Account	Points	Country	Size	Industry
ABC	1500	North America	100-500	Software
DEF	1000	MENA	500-1000	Manufacturing
GHI	900	ASEAN	1000-5000	Aerospace
JKL	1200	EU	100-500	Software
MNO	600	AN&Z	500-1000	Manufacturing
PQR	200	North America	1000-5000	Aerospace
STU	1400	ASEAN	100-500	Software
VWX	100	AN&Z	500-1000	Manufacturing
YZ	300	EU	1000-5000	Aerospace

[v-rongtiep-msft]

Hi @sanchid ,

"Based on the Points column, I need to display a relative score on a PowerBI table, which will range from 0 to 5."  The points are all greater than 100 and less than 2000, how do they appear by percentage? Could you please provide the desired output?

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanchid]

Hi @v-rongtiep-msft , Based on the points, we need to normalize them between the range of 0 to 5. I was able to achieve that using the below DAX. However the scores do not change/adjust based on my other filters. Let's say I apply industry filter on my dashboard view, I want the scores to adjust for that subset and change between 0 to 5 again.

[v-rongtiep-msft]

Hi @sanchid ,

Can you give an example of the output you want?

How to Get Your Question Answered Quickly 

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanchid]

Hi @v-rongtiep-msft, apologies. Below is my desired output without any filters. 

Account	Industry	Points	Score
ABC	Aerospace	1000	4
DEF	Manufacturing	1200	5
GHI	Software	900	4
JKL	Aerospace	700	3
MNO	Manufacturing	1000	4
PQR	Software	700	3
STU	Aerospace	500	2
VWX	Manufacturing	800	3
YZ	Software	700	3
AAA	Aerospace	300	1
ABB	Manufacturing	600	2
ACC	Software	400	1
BST	Aerospace	100	0
CAD	Manufacturing	400	1
SEJ	Software	300	1

Let's say if I filter for Aerospace industry from the dashboard filtres, I eventually want to display the following. 

Account	Industry	Points	Score
ABC	Aerospace	1000	5
JKL	Aerospace	700	3
STU	Aerospace	500	2
AAA	Aerospace	300	1
BST	Aerospace	100	0

So if we carefully look at the output difference between 1st and 2nd case, for example, account "ABC" gets scored 4 in overall picture, but gets a score of 5 when filtered for specific industry. So I want this relative score to change dynamically based on any filters applied. 

The calculation for score is as below: 

SCORE =

VAR MIN_POINTS = MIN(ACCOUNT_POINTS[POINTS])

VAR MAX_POINTS = MAX(ACCOUNT_POINTS[POINTS])

RETURN

ROUND(DIVIDE((ACCOUNT_POINTS[POINTS]-[MIN_POINTS]), ([MAX_POINTS]-[MIN_POINTS]))*5,0)

[v-rongtiep-msft · Accepted solution]

Hi @sanchid ,

Please have a try.

Create a table first.

Table 2 = SUMMARIZE('Table','Table'[Industry])

Then create a measure.

SCORE——m =
VAR _1 =
    SELECTEDVALUE ( 'Table 2'[Industry] )
VAR MIN_POINTS =
    MINX ( FILTER ( ALL ( 'Table' ), 'Table'[Industry] = _1 ), 'Table'[Points] )
VAR MAX_POINTS =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Industry] = _1 ), 'Table'[Points] )
VAR _re =
    ROUND (
        DIVIDE ( ( MAX ( 'Table'[Points] ) - MIN_POINTS ), ( MAX_POINTS - MIN_POINTS ) ) * 5,
        0
    )
RETURN
    VAR _minvalue =
        MINX ( ALL ( 'Table' ), 'Table'[Points] )
    VAR _maxvalue =
        MAXX ( ALL ( 'Table' ), 'Table'[Points] )
    VAR _re11 =
        ROUND (
            DIVIDE ( ( MAX ( 'Table'[Points] ) - _minvalue ), ( _maxvalue - _minvalue ) ) * 5,
            0
        )
    RETURN
        IF (
            _1 = BLANK (),
            _re11,
            IF ( _1 <> BLANK () && _1 = SELECTEDVALUE ( 'Table'[Industry] ), _re, BLANK () )
        )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.