Display Corresponding Measure Values on an Advance Card

[Anonymous]

I have a dynamic slicer with different measures as the options, and I want to show the location that has the highest output for the selected measure on an advanced card. I have managed to create a measure that returns the highest value for the selected measure, but I have not been able to get the corresponding location to return for that value.

This is the measure I created to display the maximum value:

Selected Measure Value = IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 1, [WHS Fixed Costs per Sq Meter MAX], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 2, [WHS Fixed Cost, % of Non-VSD Max], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 3, [WHS Var. Cost % Non-VSD Sales Max], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 4, [Avg. Cost per Hour Worked Max], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 5, [WHS Op. Cost % Non-VSD Max], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 6, [WHS Op Cost per Customer Line Shipped Max], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 7, [Outbound Transp. Cost % Non-VSD Max], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 8, [Outbound Transp. Cost per Cust Max], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 9, [Total SC Costs, % of Non-VSD Max], IF(SELECTEDVALUE('Cost Level Bar Chart'[Slicer Value]) = 10, [Total SC Costs per Non-VSD Max]))))))))))

For example, when I have one of these measures selected I am able to display the maximum value for a location based on my selection (using the measure above), but I cannot get the corresponding location to display with it.

[Anonymous]

This is what the numbers in the measure are referencing above.

[amitchandak]

You need to get the location for max value like this and then create a similar kind of filter again

location 1 = calculate(max(location[location],filter(table,table[Costs per Sq Meter MAX]= [WHS Fixed Costs per Sq Meter MAX] ))

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/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

@amitchandak 

This measure is returning the last location alphabetically and if i were to change it to MIN it returns the first location alphabetically. Is there something else to make the corresponding location to the maximum location value?

Thanks

[dax]

Hi rossjmullen, 

It seems thatyou want to show measure dynamically based on slicer, right? If so, you could create measure like below

Measure 3 = if(SELECTEDVALUE('Table'[name])="1", [SUM],IF(SELECTEDVALUE('Table'[name])="2",[avg]))

 You don't need to create select measure column in table. You could refer to my sample for details.

Best Regards,
Zoe Zhi

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

[Anonymous]

Hi @dax , 

I have already found a way to display the value for the selected measure. I am trying to find a way to also display the location that has the maximum value. There are multiple locations that have data values for the given measure and I want to see which location had the highest value.

Thanks

[dax]

Hi rossjmullen, 

If possible, could you please explain "I am trying to find a way to also display the location that has the maximum value. There are multiple locations that have data values for the given measure and I want to see which location had the highest value" for me(show the expected output)?

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

@dax @amitchandak 

So this is what the entire page looks like (masked data). Each of the selections in the "Select Measure" slicer is a separate measure I created and I was able to make a dynamic slicer that allows me to show the selected measure (which I figured out using this site: https://www.burningsuit.co.uk/blog/2018/03/dax-how-to-use-a-slicer-to-select-different-measures/ ) year over year in the bar chart on the left and what each location did for the selected year (2018 in this picture). So for the selected measure "Total Supply Chain Costs, % of Non-VSD Sales" and selected year 2018 it shows that Location7 had the highest value (right chart) and I was able to display that value (14.947) in the top right in an advance card that is circled using the long measure in my original post. I want to be also display that Location7 is the location with the highest value for the selected measure and year and a way to make it dynamically change if I change the measure and/or year.

Thanks