Forum Discussion
Post ProdigyStar Rating
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)
)
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
9 Replies
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 ?
