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.

sanchid's avatar
sanchid
Frequent Visitor
3 years ago

Hi Anonymous , 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.

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.