Forum Discussion

mdaamirkhan's avatar
mdaamirkhan
Post Prodigy
9 years ago
Solved

Star 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 s...
Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago

    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

lovermann's avatar
lovermann
Frequent Visitor
9 years ago

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

  • mdaamirkhan's avatar
    mdaamirkhan
    Post Prodigy
    9 years ago

    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's avatar
      mdaamirkhan
      Post Prodigy
      9 years ago

      any update ?

      • TomMartens's avatar
        TomMartens
        Super User
        9 years ago

        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