topic Re: Star Ratings in Quick Measures Gallery

Star Ratings

cwebb — Sun, 30 Apr 2017 09:19:03 GMT

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-data-visualisation/

eyJrIjoiYzYzZmFkMDEtZmFhZi00MzJlLWE2NGEtZGE3ZjFiOTRiYzRmIiwidCI6ImEyODQ0OTIzLTEyMTgtNGQzNi1hN2M1LTk1ODJhMzhhNGU1ZCIsImMiOjh9

Re: Star Ratings

mdaamirkhan — Mon, 17 Jul 2017 07:20:03 GMT

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)
)

Re: Star Ratings

cwebb — Fri, 21 Jul 2017 09:56:41 GMT

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

Re: Star Ratings

mdaamirkhan — Fri, 21 Jul 2017 10:23:50 GMT

I have a measure called [Max Sales]

Query is ok or not

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

Re: Star Ratings

mdaamirkhan — Sun, 23 Jul 2017 14:09:49 GMT

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

Re: Star Ratings

cwebb — Mon, 24 Jul 2017 12:43:30 GMT

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

Re: Star Ratings

mdaamirkhan — Mon, 24 Jul 2017 15:43:06 GMT

i found that solution and thanks for helping me

Re: Star Ratings

Anonymous — Tue, 25 Jul 2017 13:18:26 GMT

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

Re: Star Ratings

cwebb — Tue, 25 Jul 2017 18:44:47 GMT

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

Re: Star Ratings

safadig — Tue, 03 Apr 2018 10:53:43 GMT

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

Re: Star Ratings

Anonymous — Sat, 16 Mar 2019 06:00:27 GMT

Check out this short Youtube video as well on how to create quick measure:

Youtube video: Power BI Quick Measures

Re: Star Ratings

dcresp — Mon, 24 Jun 2019 13:55:49 GMT

cwebb

This seems to provide a half star. Is it possible to use this for a half star rating? https://www.fileformat.info/info/unicode/char/2bea/index.htm

Re: Star Ratings

skorpionaa — Tue, 24 Mar 2020 00:16:52 GMT

can we chnage the Colours of stars ?

Re: Star Ratings

justindoh — Mon, 12 Apr 2021 17:14:08 GMT

skorpionaa

Were you able to find a solution (colored star)?

Thanks.

Re: Star Ratings

bideveloper555 — Tue, 03 Aug 2021 16:02:14 GMT

This is working great but using slicer it doesnt work.

i am trying to slice based on some user names but rating doesnt work.

i am slicing on column"Name"

Any help please.

Stars =

REPT(UNICHAR(9733), CALCULATE(AVERAGE('PowerBI vwtable'[starcolumn]),ALLEXCEPT('usertable',usertable[Name])))

REPT(UNICHAR(9734), CALCULATE(5-AVERAGE('PowerBI vwtable'[starcolumn]),ALLEXCEPT('usertable',usertable[Name])))

Many thanks in Advance

Re: Star Ratings

levialae — Tue, 07 Sep 2021 14:02:31 GMT

cwebb Hi Chris

Thanks for the tool!

I have a question: Is it possible to change the formula so that you get a lower star-ranking the higher the value you score. I.e. number of defects should be as low as possible and generate more stars instead of fewer.

Re: Star Ratings

GhaziSalahuddin — Thu, 06 Jan 2022 16:04:04 GMT

Hi skorpionaa

Yes, you can change the color. Below are the steps:

1 - Click on the format menu.

2 - Click on the Field Formatting option.

3 - From the dropdown, select the rating option (or maybe the one which you have chosen while creating the rating measure).

4 - Click on Font color and choose your favorite color.

Happy Learning!

Re: Star Ratings

GhaziSalahuddin — Thu, 06 Jan 2022 16:03:19 GMT

Hi justindoh

Yes, you can change the color. Below are the steps:

1 - Click on the format menu.

2 - Click on the Field Formatting option.

3 - From the dropdown, select the rating option (or maybe the one which you have chosen while creating the rating measure).

4 - Click on Font color and choose your favorite color.

Happy Learning!

Re: Star Ratings

GU_Oliveira_SP — Mon, 10 Feb 2025 18:17:51 GMT

The column of the table influences the calculation of your measure, so it is necessary to use the CALCULATE and ALL functions.

Classification =
VAR __MAX_NUMBER_OF_STARS = 5
VAR __MIN_RATED_VALUE = CALCULATE([Measure];ALL('Table'[Column]))
VAR __MAX_RATED_VALUE = CALCULATE([Measure];ALL('Table'[Column]))
VAR __BASE_VALUE = [Measure]
VAR __NORMALIZED_BASE_VALUE =
IF(
__MAX_RATED_VALUE <> __MIN_RATED_VALUE;
MIN(MAX(DIVIDE(__BASE_VALUE - __MIN_RATED_VALUE; __MAX_RATED_VALUE - __MIN_RATED_VALUE); 0); 1);
0
)
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)
)