Star Ratings

Advisor
7758 Views
Highlighted
Advisor
Posts: 71
Registered: ‎06-25-2015

Star Ratings

This quick measure displays a score or rating out of five as a series of filled and unfilled stars. In the example in the report, there is a table called restaurants and a numeric column on that table called Score that contains a rating out of five. This measure takes the value in the Score column and displays the same number of filled stars (using the DAX Unichar() function) followed by whatever number of unfilled stars are needed to make the total number of stars up to five.

 

DAX:

 

Stars =

REPT(UNICHAR(9733), AVERAGE('Restaurants'[Score]))

&

REPT(UNICHAR(9734), 5-AVERAGE('Restaurants'[Score]))

 

For more details and other examples of how you can use this technique, see my blog post on the subject at:

https://blog.crossjoin.co.uk/2017/04/11/the-dax-unichar-function-and-how-to-use-it-in-measures-for-d...

Attachment
Member
Posts: 208
Registered: ‎07-06-2017

Re: Star Ratings

Hello Everyone,

 

I have created quick measure "Star Rating" based on sales value but I dont the Value for highest star rating. Sales value either be Million or Billion or less.

 

How I want to set MAX_RATED_VALUE dynamic based on sales? Can you please help me on this ?

 

Sales_LCD star rating =
VAR __MAX_NUMBER_OF_STARS = 5
VAR __MIN_RATED_VALUE = 0
VAR __MAX_RATED_VALUE = 1000000
VAR __BASE_VALUE = SUM('Pfizer Champix QTR'[Sales_LCD])
VAR __NORMALIZED_BASE_VALUE =
MIN(
MAX(
DIVIDE(
__BASE_VALUE - __MIN_RATED_VALUE,
__MAX_RATED_VALUE - __MIN_RATED_VALUE
),
0
),
1
)
VAR __STAR_RATING = ROUND(__NORMALIZED_BASE_VALUE * __MAX_NUMBER_OF_STARS, 0)
RETURN
IF(
NOT ISBLANK(__BASE_VALUE),
REPT(UNICHAR(9733), __STAR_RATING)
& REPT(UNICHAR(9734), __MAX_NUMBER_OF_STARS - __STAR_RATING)
)

 

Advisor
Posts: 71
Registered: ‎06-25-2015

Re: Star Ratings

You need to create another measure in your report that returns the max value, and then replace the hardcoded max value (1000000 in your example) with the name of the measure.

 

So, for example, if you created a measure called [Max Sales] that returned the maximum sales visible on your report (this might be quite difficult to do though...) then the highlighted line would be:

 

VAR __MAX_RATED_VALUE = [Max Sales]

 

 

Regards,

 

Chris

Member
Posts: 208
Registered: ‎07-06-2017

Re: Star Ratings

I have a measure called [Max Sales]  

Query is ok or not

 

MaxSales = MAX('Pfizer Champix QTR'[Sales_LCD])

Member
Posts: 208
Registered: ‎07-06-2017

Re: Star Ratings

I already tried that its showing 5-stars in all sales but where sales vlue is 0 it showing not stars.

Advisor
Posts: 71
Registered: ‎06-25-2015

Re: Star Ratings

The expression MAX('Pfizer Champix QTR'[Sales_LCD]) will give you the maximum value found in a row in the 'Pfizer Champix QTR' table in your data model, which is not necessarily the maximum value displayed in your visualisation. If you add it to your table as a separate column, does it give you the values you expect? My guess is that you'll need to use the MAXX() function in a more complex measure, and not the MAX() function here:

 

https://msdn.microsoft.com/en-us/library/ee634576.aspx

 

Chris

Member
Posts: 208
Registered: ‎07-06-2017

Re: Star Ratings

i found that solution and thanks for helping me

Frequent Visitor
Posts: 16
Registered: ‎07-22-2016

Re: Star Ratings

Hi chris, if the avg rating is 4.5 or 3.5 will it show half filled stars? or how can we do it.

Advisor
Posts: 71
Registered: ‎06-25-2015

Re: Star Ratings

As far as I can see there's isn't a unicode character for a half-filled star, so it would not be possible to use this technique to display half-filled stars.

 

Sorry!

 

Chris

Visitor
Posts: 3
Registered: ‎03-26-2018

Re: Star Ratings

A solution would be to:

 

1. Create an image for each star rating you liked displayed ( including half ratings).

2. Upload image to a URL.

3. Create calculated column in data based on rating.

4. Add Column by example that include Properly named image URL column to reflect your rating image.

5. Change Poperties of URL Column under Modeling to image url.

6. Use you new URL image column in the table to show the ratings