Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculate Maximum value based on Slicer Selection

Hi All,

 

I just wanted to calculate the maximum percentage(Best performer) based on the slicer selection (Quarter, Month, Year).

I also need to calculate the best performer based on various categories.

Lets say Iif i select Q3 in the slicer, the max value of the best performer should be visible.

 

Thanks, 

Need Help
Show and Tell
Tips and Tricks

10 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    do you want 1 slicer for month(which implies quarter and year) or 3 slicers, one for month, one for quarter, one for year ?

    what data is the percentage being calculated on ? i assume there is a date and an amount, but what is it a percentage of ?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I want 3 slicers, one for each month, Quarter and Year. I want category slicer 1 as well.

       
       

      I want to calculate the maximum family satisfaction score based on category slicer 1 and in a given time period.

       
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

        Write the following calculated column formula to generate a date in this Table

        Date = 1*("1/"&Data[Month]&"/"&Data[Year])

        Format this column as Date.  Now go to Modelling > New Table and write this formula to create a Calendar Table

        Calendar = CALENDAR(MIN(DATA[Date]),MAX(DATA[Date]))

        Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  In the Calendar Table, write these calulated column formula to extract Year, Month and Qusrter

        Year = IF(MONTH(Calendar[Date])>=4,Year(Calendar[Date])&"-"&Year(Calendar[Date])+1,Year(Calendar[Date])-1&"-"&Year(Calendar[Date]))

        Month = FORMAT(Calendar[Date],"mmmm")

        Quarter = IF(MONTH(Calendar[Date])>=1,"Q4",IF(MONTH(Calendar[Date])>=10,"Q3",IF(MONTH(Calendar[Date])>=7,"Q2","Q1")))

        Build 3 slicers - one each for Year, Month and Quarter from the Calendar Table.  Select any one Year, Month and Year.  Build another slicer for Category slicer 1 column and make any selection there.

        Write this measure

        Measure = MAX(Data[Family satisfaction score])

        Hope this helps.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Assume your measure is Amount from sales. Calculate a measure like

    sales = calculate(sum(sales[Amount]))
overall sales = calculate(sum(sales[Amount]),all(sales)

% of total = divide(sales ,overall sales)

     

    You can also try quick measures

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks.

    My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

     

     

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You create columns Year, Quarter and Month like DAX below.

     

    Year=YEAR(Table1[Date])
 
Quarter =ROUNDUP(MONTH(Table1[Date])/3,0)
 
Month=MONTH(Table1[Date])

     

    Then you can create measures Percentage , Rank and Filter1, put the Filter1 in the Visual Level Filter of table visual which displays the Year, Quarter, Month ,Category Slicer 1 , Category Slicer 2 and Percentage, setting Filter1 as "is not blank".

     

    Percentage=DIVIDE(SUM(Table1[Value]), CALCULATE(SUM(Table1[Value]), ALL(Table)))
 
Rank= RANKX(ALLSELECTED(Table1),Table1[Percentage],,DESC ,Dense)
 
Filter1=IF([Rank]=1, 1, BLANK())

     

    Finally, when you make some selections in slicers, the table visual will return the related maximum percentage record.

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.