Gap between highest and lowest values of a measure

DAX DAX Calculations
1

Hi - I’m back!

I’m stuck on this new thing.

I have a measure - average (score) - this is an average of a batch of indicators for a given city.

The cities are in categories - say A and B.

I have a bar chart for each category of cities, showing their average score. All is fine.

Now I want to dynamically calculate the GAP between the highest and lowest in each of the cities in my chart (Category A and Category B).

I can’t create a min and max of a measure. That much I know (but not much else).

So how can I get the gap say between 55% and 23% for one category and 66% and 34% for the other category. Dynamically of course!

All help would be much appreciated.

I haven’t included a dataset… not sure it’s needed… but I will anonymize data if I need to!!

Mega thanks Erica

2

Best way I can think to do this would be to create virtual tables for your Min and Max amounts, and then subtract those.

Example:

Highest Avg = 
    MAXX(
        SUMMARIZE(
        Customers, Customers[Customer Names], Customers[Customer Index],
        "@AVG_AMT", [Average]),
    [@AVG_AMT] )

same logic for lowest average, then subtract the difference:

Average  Diff = 
     [Highest Avg] - [Lowest Avg]

See attached example
eDNA Solution - Min and Max Variance.pbix (374.2 KB)

1 Like
3

Thanks a lot Heather. I thought it might be something like that. Will give it a go. This is an example of the output I’m trying to get to

edna forum
edna forum1174×672 56.3 KB

I’ll give your helpful suggestion a whirl and hopefully I can get back to you with a big smile on my face!!

1 Like
4

Hi,

If you want to achieve the above output assuming the percentage as per of total for one category.
I think you need to create a cal table form which you can dynamical find out your avg gap max and min .

If i get your output right you can give my idea a try.

Thanks,
Anurag

1 Like
5

Yeah! I have a BIG smile on my face thanks to you pointing me in the right direction!!

I managed to create filtered virtual tables (so the user didn’t need to use a slicer) and then create min and max score… but then I also created without a filter inside the measures… and I think this is better because it enables me to create one graph and table that I can filter depending on the categories.

So yes, a big THANK YOU for pointing me in the right direction. I now have min and max score for each category and each indicator or group of indicators… Wonderful! Moved up another notch regarding virtual tables!

2 Likes
6

glad that helped you out - please close this thread by marking the appropriate response as a solution. :slight_smile: