topic Re: How to calculate a ranking score between 1 & 5 in DAX Commands and Tips

How to calculate a ranking score between 1 & 5

ClemFandango — Tue, 12 Nov 2024 10:35:02 GMT

Hi there, I am having a bit of a maths & dax failure and hoping that one of you amazing people can help.

I am trying to calculate a ‘5 star rating’ for the ‘completed’ column in relation to the average number of ‘completed’. The idea is to provide a ranking of how many each person has completed. Please see example below of the rating that I am trying to achieve in a table visual. The column '_5_star_rating' shows a small graphic of stars 0 to 5.

Table visual

Name	Completed	_5_star_rating (out of 5)
Person1	700	5
Person2	200	2
Person3	414	3

The ‘Name’ and ‘Completed’ columns are taken from ‘table1’ and look something like this:-

Name	Completed
Person1	1
Person1	1
Person2	1
Person3	1

i.e Person1 will have 700 rows that = 1, Person2 will have 200 rows that = 1 & Person3 will have 414 rows that = 1

This is the measure that I currently have for the ‘_5_star_rating’.

_5_star_rating = Var _star = INT(DIVIDE(SUM(table1[Completed]),100)) VAR _unstar = 5 - _star RETURN REPT(UNICHAR(11088),_star)

It partly works. It currently returns a graphic of stars 0 to 5, but the calculation doesnt work out the 1 to 5 ratings in relation to an overall ranking for each person under 'Name'

Any ideas eternally appreciated!

Thanks as always

Re: How to calculate a ranking score between 1 & 5

FarhanJeelani — Tue, 12 Nov 2024 10:59:15 GMT

To calculate a 5-star rating that ranks each person’s "Completed" count relative to others, you can normalize the "Completed" values between 1 and 5 based on their relative position. Here's how you can approach this in DAX.

Step-by-Step Solution

1. Calculate the Minimum and Maximum Completed Values: First, you need the minimum and maximum values of the "Completed" column to create a scale.

2. Normalize the Score: Use these values to scale each person’s "Completed" count between 1 and 5. This way, the lowest "Completed" count will be 1 star, and the highest will be 5 stars.

3. Generate Star Symbols: Convert this scaled score to a visual star rating by repeating a star symbol.

DAX Measure for 5-Star Rating

Here's a DAX measure to implement the above steps:

DAX

_5_star_rating = VAR MinCompleted = CALCULATE(MIN(table1[Completed]), ALL(table1)) VAR MaxCompleted = CALCULATE(MAX(table1[Completed]), ALL(table1)) VAR CompletedCount = SUM(table1[Completed]) // Normalize the Completed count between 1 and 5 VAR NormalizedScore = 1 + ((CompletedCount - MinCompleted) / (MaxCompleted - MinCompleted)) * 4 // Round the normalized score to an integer VAR StarRating = ROUND(NormalizedScore, 0) // Create star symbols RETURN REPT(UNICHAR(11088), StarRating)

Notes

- This measure dynamically adjusts the rating based on the range of "Completed" values in your data, so it should work even if new names or completed counts are added.
- You can replace `UNICHAR(11088)` with a different symbol if you prefer a different star style.

Example Output

For the table you provided:

| Name | Completed | _5_star_rating (out of 5) |
|----------|-----------|---------------------------|
| Person1 | 700 | ⭐⭐⭐⭐⭐ |
| Person2 | 200 | ⭐⭐ |
| Person3 | 414 | ⭐⭐⭐ |

This should yield a more accurate star ranking based on each person's completed count relative to the others. Let me know if you need further adjustments!

Please accept this as solution if it helps. Appreciate Kudos.

Re: How to calculate a ranking score between 1 & 5

DataNinja777 — Tue, 12 Nov 2024 11:04:22 GMT

ClemFandango ,

To achieve the 5-star rating based on each person's completion count relative to the average completion count, we need to calculate how each individual’s completion count compares to the average. Here’s an approach using DAX:

Calculate the Average Completion: Compute the average of completed tasks across all people.
Determine the 5-Star Rating Scale: Scale each person’s completion count relative to the average.
Generate Stars as a Visual Representation: Use UNICHAR to display stars based on the calculated rating.

_5_star_rating = VAR _AvgCompleted = AVERAGEX(ALL(table1), CALCULATE(SUM(table1[Completed]))) VAR _Completed = SUM(table1[Completed]) VAR _RatingScale = DIVIDE(_Completed, _AvgCompleted) VAR _star = INT(MIN(5, ROUNDUP(_RatingScale, 0))) // Limit rating to max of 5 RETURN REPT(UNICHAR(11088), _star) // Unicode for a filled star

This measure should dynamically adjust each person’s rating based on their performance relative to the group average. Adjust the scaling factor if you need to fine-tune the distribution across the 5-star range.

Best regards,

Re: How to calculate a ranking score between 1 & 5

ClemFandango — Tue, 12 Nov 2024 12:22:32 GMT

Hi both!

Very impressive and super quick!

Unfortunatley, I still cant get it to work - obviously it is something that I am doing wrong.

DataNinja777 the result i get using your measue is 5 stars for everything, with the occational blank star

FarhanJeelani i get 100's of stars using this methodology

Could it be something to do with my 'Completed' calculated column?

Completed = IF( ('Table1'[Type] IN {"a", "b", "c"} && ' Table1'[Status] = "Completed" && MONTH('Table1'[End]) = MONTH(TODAY()) - 1 && YEAR(' Table1'[End]) = YEAR(TODAY())) || (' Table1'[Type] = "d" && ' Table1'[Status] = "Completed" && MONTH(' Table1'[Last Updated]) = MONTH(TODAY()) - 1 && YEAR(' Table1'[Last Updated]) = YEAR(TODAY())) || (' Table1'[Type] IN {"a", "b", "c"} && ' Table1'[Status] = "Completed" && MONTH(' Table1'[End]) = MONTH(TODAY()) && YEAR(' Table1'[End]) = YEAR(TODAY())) || (' Table1'[Type] = "d" && ' Table1'[Status] = "Completed" && MONTH(' Table1'[Last Updated]) = MONTH(TODAY()) && YEAR(' Table1'[Last Updated]) = YEAR(TODAY()) ),calculate (COUNTROWS(Table1), allexcept(Table1, Table1 [ID])),0)

The above 'Completed' column returns a 1 where all of the above conditions are met. It works in terms of what I am trying to calculate in the column, it just doesnt calculate correctly in the '_5_star_rating' measures. Could this be because I also have 0's? (i forgot the mention these in my first post)

Table1

Name	Completed
Person1	1
Person1	1
Person1	0
Person2	1
Person3	0
Person3	1

i.e Person1 will have 700 rows that = 1, Person2 will have 200 rows that = 1 & Person3 will have 414 rows that = 1

Any ideas where I am going wrong?

Re: How to calculate a ranking score between 1 & 5

FarhanJeelani — Tue, 12 Nov 2024 12:19:36 GMT

Yes, the issue could be related to the `Completed` calculated column. From your description, it looks like the `Completed` column may be returning values that are too large, which is why you're seeing hundreds of stars when you calculate the rating.

Why is this happening?

The `Completed` calculated column is using a `COUNTROWS` function, which returns the count of rows for each ID, and it is not aggregated in a way that scales properly for your rating calculation. In your case, if `COUNTROWS` is returning large numbers, that could cause the `SUM(table1[Completed])` in your `_5_star_rating` measure to become disproportionately large, resulting in many stars.

Potential Fix:

1. Check the Logic of the `Completed` Column: The `Completed` column logic you provided counts the number of rows that meet certain criteria, and that count may be too large when summed across all rows in `table1`. You might want to check the values of `Completed` in your data to confirm they are appropriate for your rating calculation.

2. Reconsider the Aggregation in the `_5_star_rating` Measure: Instead of summing `Completed`, you may need to adjust how you are calculating the average completed count. For example, consider directly calculating the rating based on a more refined logic that takes into account the counts or percentages instead of raw row counts.

Modified DAX for `_5_star_rating`:

Try updating the `_5_star_rating` measure with a more controlled logic that ensures the `Completed` values are appropriately handled:

DAX

_5_star_rating = VAR _AvgCompleted = AVERAGEX(ALL('table1'), CALCULATE(SUM('table1'[Completed]))) VAR _Completed = SUM('table1'[Completed]) VAR _RatingScale = DIVIDE(_Completed, _AvgCompleted) VAR _star = INT(MIN(5, ROUNDUP(_RatingScale, 0))) // Limit rating to a max of 5 stars RETURN REPT(UNICHAR(11088), _star) // Unicode for a filled star

In this formula:
- SUM('table1'[Completed]): This calculates the sum of the `Completed` column values for the current context.
- AVERAGEX(ALL('table1'), ...): This is calculating the average of the `Completed` values across all rows in `table1`, removing any filters.

If the `Completed` column still has an unexpectedly high value, consider modifying the column definition to avoid large counts or use a different aggregation method (e.g., percentages or a simple flag indicating completion).

Debugging Steps:

- Step 1: Check the actual values in the `Completed` column for a few rows. This will help confirm if the counts are indeed too large.
- Step 2: Test your `_5_star_rating` measure with a simple sum or average to see if the calculated values make sense.

This will help you control the number of stars displayed and ensure the rating is calculated correctly.

Re: How to calculate a ranking score between 1 & 5

ClemFandango — Tue, 12 Nov 2024 14:05:58 GMT

FarhanJeelani thanks for the detailed explanation.

I think you have nailed the bit that i am struggling with.

This is what the Table visual currently looks like with the _5_star_rating measure broken down.

Name	_Completed	_5_star_rating (not working)	_AvgCompleted	_RatingScale
Person1	66	5	0.13	490.66
Person2	55	5	0.13	408.88
Person3	31	5	0.13	230.46

The table used to calculate _completed looks like this

Table1

Name	Completed
Person1	1
Person1	1
Person1	0
Person2	1
Person3	0
Person3	1

i.e Person1 will have 66 rows that = 1, Person2 will have 55 rows that = 1 & Person3 will have 31 rows that = 1. No person has more than 1 in the 'Completed' column, but there are thousands of zeros.

This acts as a simple flag to return 1, if a specific data criteria is met.

Am i correct in thinking that the '_AvgCompleted' of 0.13 is probably counting the zeros and throwing off the _5_star_rating?

Re: How to calculate a ranking score between 1 & 5

DataNinja777 — Tue, 12 Nov 2024 14:28:05 GMT

Hi ClemFandango ,

To increase variability, let's ensure the percentile ranking logic distributes values correctly by introducing a more granular approach. We’ll adjust by using the ranking and dividing it by the total count to create true percentiles, so each record falls into a more precise range for the 1-5 star ratings.

_5_star_rating = VAR _Completed = SUM(table1[Completed]) VAR Ranking = RANKX(ALL(table1), CALCULATE(SUM(table1[Completed])), , DESC, DENSE) VAR TotalCount = COUNTROWS(ALL(table1)) VAR PercentileRank = DIVIDE(Ranking, TotalCount + 1) // +1 to adjust rank-to-percentile scaling VAR _star = SWITCH( TRUE(), PercentileRank <= 0.2, 5, // Top 20% get 5 stars PercentileRank <= 0.4, 4, // Next 20% get 4 stars PercentileRank <= 0.6, 3, // Middle 20% get 3 stars PercentileRank <= 0.8, 2, // Next 20% get 2 stars 1 // Bottom 20% get 1 star ) RETURN REPT(UNICHAR(11088), _star) // Unicode for a filled star

The resulting output is as shown below:

Best regards,

Re: How to calculate a ranking score between 1 & 5

FarhanJeelani — Tue, 12 Nov 2024 18:17:31 GMT

Yes, you're absolutely correct. The `_AvgCompleted` measure is likely including the zeros in its calculation, which is why it's producing a very low average (0.13). To fix this, you should modify `_AvgCompleted` to only average non-zero entries in the `Completed` column, which will give you the actual average for entries that meet the "Completed" criteria.

Updated `_AvgCompleted` Calculation

You can adjust `_AvgCompleted` to ignore zeros by using the `FILTER` function to only consider rows where `Completed` is 1. Here’s the revised measure:

DAX

_AvgCompleted = AVERAGEX(FILTER(ALL('table1'), 'table1'[Completed] = 1), 'table1'[Completed])

This change will calculate the average for only the rows where `Completed` equals 1, which should correct the `_5_star_rating` calculation.

Revised `_5_star_rating` Measure

Now that `_AvgCompleted` accurately represents only the completed tasks, your `_5_star_rating` calculation should work as expected:

DAX

_5_star_rating = VAR _AvgCompleted = AVERAGEX(FILTER(ALL('table1'), 'table1'[Completed] = 1), 'table1'[Completed]) VAR _Completed = SUM('table1'[Completed]) VAR _RatingScale = DIVIDE(_Completed, _AvgCompleted) VAR _star = INT(MIN(5, ROUNDUP(_RatingScale, 0))) // Limit rating to a max of 5 stars RETURN REPT(UNICHAR(11088), _star) // Unicode for a filled star

Explanation
1. _AvgCompleted: Averages only the rows where `Completed = 1`.
2. _Completed: Sums the completed tasks for each person.
3. _RatingScale: Divides the individual’s completed tasks by the adjusted average.
4. _star: Rounds up the rating scale, capping it at a maximum of 5 stars.

With this adjustment, `_5_star_rating` should now accurately reflect the intended star rating based on the average completion of non-zero entries.

Please mark this as solution if it helps you. Appreciate Kudos.😊

Re: How to calculate a ranking score between 1 & 5

ClemFandango — Thu, 14 Nov 2024 07:39:39 GMT

Thanks DataNinja777 FarhanJeelani again!

Your suggestions and guidance are really helping me to understand this. Unfortunatley, I am unable to get this working (it returns 5 stars for all 'persons') as i still have a couple of blindspots in my understanding.

I think my dataset is causing the issues and I will try to outline the problems below.

This is the measure that I am using:-

This is a breakdown of the results returned inside the measure:-

VAR _Completed = this is correctly calculating the sum of the ammount of rows 'completed' by 'person'
VAR Ranking = this returns a 1 for any 'person' that has a sum 'completed' >=1. Where a 'person' has 0 'completed' it returns 2. I am not sure if the number of the results returned by 'table1' is causing issues. A breakdown of the measure is = RANKX(ALL(table1) 'this = 93482', CALCULATE(SUM(table1[Completed])) 'this = 1263 for the whole table and max of 326 by person', , DESC, DENSE)

VAR TotalCount = This returns 93,482
VAR PercentileRank = this returns 0.00001066 for any 'person' that has a sum 'completed' >=1. Where a 'person' has 0 'completed' it returns 0.00002131

Am i correct in thinking that it is not working because my 'totalcount' is too large? Is there any way around this without reducing 'totalcount' in the dataset?

Another thing worth mentioning is that the maximum VAR _Completed for any 'person' is about 326 and my dataset includes 30 'persons'. The total 'completed' for the whole table is 1263.

Is there anything that can be done to get this working in my unweildy dataset?

Yours forever indebted

Re: How to calculate a ranking score between 1 & 5

DataNinja777 — Thu, 14 Nov 2024 12:01:17 GMT

Hi ClemFandango ,

Given the large dataset size, your PercentileRank is indeed returning very small values, which is why everyone seems to fall into the top star rating (5 stars). Let's address this by ranking each person only relative to the other persons instead of all 93,482 records.

Here’s a refined approach:

Calculate Ranking by Person: We’ll rank based on total “Completed” per person.
Calculate Total Person Count: Instead of counting all rows, we’ll count the unique persons.
Calculate Percentile Rank by Person Count: This will give a more realistic percentile range.

Try this adjusted measure:

_5_star_rating = VAR _Completed = SUM(table1[Completed]) VAR TotalPersons = COUNTROWS(SUMMARIZE(ALL(table1), table1[Person])) VAR PersonRanking = RANKX( ALL(table1[Person]), CALCULATE(SUM(table1[Completed])), , DESC, DENSE ) VAR PercentileRank = DIVIDE(PersonRanking, TotalPersons + 1) // Rank to percentile VAR _star = SWITCH( TRUE(), PercentileRank <= 0.2, 5, // Top 20% get 5 stars PercentileRank <= 0.4, 4, // Next 20% get 4 stars PercentileRank <= 0.6, 3, // Middle 20% get 3 stars PercentileRank <= 0.8, 2, // Next 20% get 2 stars 1 // Bottom 20% get 1 star ) RETURN REPT(UNICHAR(11088), _star) // Unicode for a filled star

TotalPersons: Calculates the unique count of persons rather than the total rows.
PersonRanking: Ranks each person based on their total "Completed" count, which should be a value from 1 to 30.
PercentileRank: Divides the ranking by the total person count, which will give meaningful percentiles for the star rating.

This revised measure should result in a more accurate distribution of stars across persons. Let me know how this works with your dataset!

Best regards,

Re: How to calculate a ranking score between 1 & 5

ClemFandango — Mon, 18 Nov 2024 13:44:52 GMT

Hellooooo DataNinja777 ,

Looking at this with fresh eyes after the wk/end I was able to solve it using your methodology, with the code below:-

_5_star_rating = VAR _Completed = SUM(Table1[Completed]) VAR TotalPersons = COUNTROWS(SUMMARIZE(ALL(Table1), Activities[Person])) VAR PersonRanking = RANKX(ALL(TeamCurrent[person]), CALCULATE (sum(Table1[Completed]), REMOVEFILTERS (TeamCurrent[Role],TeamCurrent[Team] ), VALUES ( 'Accounts'[isYes])),,DESC,Dense ) VAR PercentileRank = DIVIDE(PersonRanking, TotalPersons + 1) // Rank to percentile VAR _star = SWITCH( TRUE(), PercentileRank <= 0.2, 5, // Top 20% get 5 stars PercentileRank <= 0.4, 4, // Next 20% get 4 stars PercentileRank <= 0.6, 3, // Middle 20% get 3 stars PercentileRank <= 0.8, 2, // Next 20% get 2 stars 1 // Bottom 20% get 1 star ) RETURN REPT(UNICHAR(11088), _star) // Unicode for a filled star

The only bit I am struggling with is the Var PersonRanking. It currently ranks all of the 'persons' from 1 to 37 (with rank 1 having the most 'Completed', rank 36 has 1 'Completed' and rank 37 has 0 'Completed'). The problem is that I have 6 'persons' that have a ranking of 37 (as they have 0 'Completed'), and this distorts the PercentileRank slightly.

Is there a way to ignore 'persons' in the ranking that have 0 'completed'?

Many many thanks,

Re: How to calculate a ranking score between 1 & 5

Anonymous — Wed, 20 Nov 2024 09:28:25 GMT

Hi,ClemFandango
Has the problem been solved? If it does, share your solution and accept it as a solution that will help other community members who have the same problem as you.

As we are unclear about the structure of your data, we are currently unable to make a complete inference or test based on the DAX you provided. If you require our assistance, I recommend that you provide the data structures for your tables: Table1, TeamCurrent, Activities, and Accounts, as well as the relationships between these tables. This will enable us to analyse your issue using virtual test data and assist you more effectively.

When uploading a file, please be careful to delete sensitive information.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Additionally, regarding your current issue, you might also consider adapting the filters directly to resolve it.

For further details, please refer to:

Add a filter to a report in Power BI - Power BI | Microsoft Learn

Best Regards,

Leroy Lu