Star Rating

[mdaamirkhan]

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

[lovermann]

It'd be better to show the table you have and describe the table you want to have.

[mdaamirkhan]

I have attached the screenshot for Star Rating with table and query. In screenshot there sales table and Star Rating. I have set the Var _Max_RATED_VALUE = 1000000 manually  but how I will replace this "1000000" by dynamically by sales in Var _Max_RATED_VALUE. supposed I dont know the sales value its in Million or Billion or less value?

[mdaamirkhan]

any update ?

[TomMartens]

Hey,

you already calculate the value for the variable

VAR __BASE_VALUE = SUM('Pfizer Champix QTR'[Sales_LCD])

Now try something like this for the variable

VAR __MAX_RATED_VALUE = 
CALCULATE(
  MAXX('your table', 'your table'[column that contain the values])
)

Hope this helps

[mdaamirkhan]

I have tried your below query and showing all 5 stars which is wrong. Not working 

[TomMartens]

Hey,

can you prepare some sample data and share a link for downloading the data.

[mdaamirkhan]

I have prepared the sample data and shared the link : https://drive.google.com/open?id=0BxreilNC4AvsMW9QaWFUSGFZYlk

[TomMartens · Accepted solution]

Hey,

thanks a lot for sharing your data.

Here you find a very simplified solution and here is an approach using your data

There is a difficulty how to determine the max value for "SALES_LCD", it's easy to use this DAX statement

MAXX SALES_LCD = 
MAXX(ALLSELECTED(data),data[Sales_LCD])

This statement returns the max value from the data table in a row based context. I created a little, little report thas show that for panel "FRANCE OFF-TAKE" the values SALES_LCD and MAXX ... do not match:

 This mismatch is due that there are more than one row for this panel in current filter context.

Within the simplified solution you also find another approach, using SUMMARIZE to aggregate Sales_LCD based on certain group by columns. This appoach delivers the max value in the current filter context but needs one or more grouping columns, but these grouping columns may depend on the selection of the usere. If you design this measre for a "fixed" report, where you know what is used in rows or columns you can use SUMMARIZE.

Hope this helps

[mdaamirkhan]

Thanks you very much