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 ,

"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'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.