Forum Discussion

sanchid's avatar
sanchid
Frequent Visitor
3 years ago
Solved

Display Percentiles

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...
Interesting Links
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
sanchid
Frequent Visitor
3 years ago

Hi Anonymous, apologies. Below is my desired output without any filters. 

 

AccountIndustryPointsScore
ABCAerospace10004
DEFManufacturing12005
GHISoftware9004
JKLAerospace7003
MNOManufacturing10004
PQRSoftware7003
STUAerospace5002
VWXManufacturing8003
YZSoftware7003
AAAAerospace3001
ABBManufacturing6002
ACCSoftware4001
BSTAerospace1000
CADManufacturing4001
SEJSoftware3001

 

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

 

AccountIndustryPointsScore
ABCAerospace10005
JKLAerospace7003
STUAerospace5002
AAAAerospace3001
BSTAerospace1000

 

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)
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.