Calculate Maximum value based on Slicer Selection

[Anonymous]

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, 

[v-xicai]

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.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[amitchandak]

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

[kentyler]

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]

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]

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.

[Anonymous]

@Ashish_Mathur 

Is it necessary to create a seperate table(Calender table)?
Please note that i do have a date column in my dataset.

[Ashish_Mathur]

Yes, it is necessary.  That is what allows you to use the Date and Time Intelligence functions.

[Anonymous]

Hi,

I want to pass slicer selection of month and category(multiple values) in calculating the Max family satisfaction score.How can i do that?

Thanks,
Abrarali Surti

[Ashish_Mathur]

That is what my solution should do for you.  Please follow the steps outlined in my previous post.