Max of Summed Values of a Category

[badger123]

Hello,

I am hoping for some help with the following, after trying and failing numerous times. I have created two dummy tables for simplicity (see below). These tables have a one-to-many relationship based on the ID.

I am creating a page in my report that focuses on one Item in one Country at a time. There are various slicers, including Region. I am looking to create some benchmarks to compare the selected Item with its Category benchmarks. The benchmark I am struggling to create is the Max total Volume per Category, let's call this MaxBenchmark.

This is how I want it to work. If I select Item 1 and Country UK, total Volume would be 350 and MaxBenchmark should be 2,300 because Item 3 has the highest total Volume in Category A. If I select Item 2 and Country UK, total Volume would be 850, and MaxBenchmark should also be 850 because Item 2 has the highest total vol in Category B. An added complication, if I now select Region UKE, total Volume would be 50 and MaxBenchmark should be 100, because Item 4 has the highest Volume per Category in that Region. 

Table1Table2

I have been going round in circles with this for days so any guidance would be much appreciated! 

Thanks 🙂

[AlB · Accepted solution]

Hi @badger123

We'll create the two measures you talk about and the you can use them as you like in the gauge.

1. Set the Table2[ID] and Table1[Country] and Table1[Region] in slicers as you described
2. Create the measure for the selected item volume (which I believe you have already):
 

VolumeMeasure = SUM(Table1[ID])

3. Create the measure for the Max:

MaxBenchmark =
VAR _ItemCategory =
    SELECTEDVALUE ( Table2[Category] )
VAR _CategoryVolumes =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( Table2[ID] );
            Table2[Category] = _ItemCategory;
            ALL ( Table2 )
        );
        "VolumeCol"; [VolumeMeasure]
    )
VAR _MaxInCat =
    MAXX ( _CategoryVolumes; [VolumeCol] )
RETURN
    _MaxInCat

Code formatted with  

[AlB]

Hi @badger123

How would you want this? In two Card visuals, one with TotalVolume and one with  MaxBenchMark?

Can you please paste the data in table /text instead of on a screen capture? So that it can be easily copied?

[AlB · Accepted solution]

Hi @badger123

We'll create the two measures you talk about and the you can use them as you like in the gauge.

1. Set the Table2[ID] and Table1[Country] and Table1[Region] in slicers as you described
2. Create the measure for the selected item volume (which I believe you have already):
 

VolumeMeasure = SUM(Table1[ID])

3. Create the measure for the Max:

MaxBenchmark =
VAR _ItemCategory =
    SELECTEDVALUE ( Table2[Category] )
VAR _CategoryVolumes =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( Table2[ID] );
            Table2[Category] = _ItemCategory;
            ALL ( Table2 )
        );
        "VolumeCol"; [VolumeMeasure]
    )
VAR _MaxInCat =
    MAXX ( _CategoryVolumes; [VolumeCol] )
RETURN
    _MaxInCat

Code formatted with  

[badger123]

Hi @AlB,

Since you were so helpful this, I'm wondering how to create a similar measure, except this time dealing with the weighted average of Growth for the selected Category. I have a measure that calculates the weighted average for one item (see below), but struggling to create a measure that successfully calculates the weighted average for a Category. 

Weighted_Average_Growth = DIVIDE (

SUMX ( 'Table 2', 'Table 2'[growth] * 'Table 2'[volume] ),

CALCULATE ( SUM ( 'Table 2'[volume] ), ALLSELECTED('Table 2'[id])))

ID	ID2	Country	Region	Volume	Growth
1	1C	UK	UKC	100	10%
1	1D	UK	UKD	200	20%
1	1E	UK	UKE	50	30%
2	2C	UK	UKC	500	10%
2	2D	UK	UKD	300	50%
2	2E	UK	UKE	50	60%
3	3C	UK	UKC	1000	100%
3	3D	UK	UKD	800	150%
3	3E	UK	UKE	500	200%
4	4C	UK	UKC	100	-30%
4	4D	UK	UKD	200	-40%
4	4E	UK	UKE	100	-10%

[AlB]

@badger123

Try this, with Table2[Category] in the slicer and the measure for instance in Card visual.

Table2 is as previously, Table3 the new one that you show with the growth.

WeightedAverageGrowthCat =
DIVIDE (
    SUMX ( Table3; Table3[Growth] * Table3[volume] );
    SUM ( Table3[volume] )
)

Code formatted with  

[badger123]

Hi @AlB

I don't want to use a slicer for category, I'm looking to do the same as before where an item is selected and the weighted average should be calculated based on the corresponding category. I'm guessing I will need to take a similar approach combining this... 

VAR _ItemCategory =
    SELECTEDVALUE ( Table2[Category] )

and this... 

WeightedAverageGrowthCat =
DIVIDE (
    SUMX ( Table3; Table3[Growth] * Table3[volume] );
    SUM ( Table3[volume] )
)

but don't quite know what that measure would be?!  

[badger123]

Hi @AlB

I don't want to use a slicer for category, I'm looking to do the same as before where an item is selected and the weighted average should be calculated based on the corresponding category. I'm guessing I will need to take a similar approach combining this... 

VAR _ItemCategory =
    SELECTEDVALUE ( Table2[Category] )

and this... 

WeightedAverageGrowthCat =
DIVIDE (
    SUMX ( Table3; Table3[Growth] * Table3[volume] );
    SUM ( Table3[volume] )
)

but don't quite know what that measure would be?!  

[AlB]

@badger123

Yeah you could follow the same logic, or change it a bit:

Table2 is as previously, Table3 the new one that you show with the growth.

WeightedAverageGrowthCat_V2 =
VAR _ItemCategory =
    SELECTEDVALUE ( Table2[Category] )
RETURN
    CALCULATE (
        DIVIDE (
            SUMX (
                Table3;
                Table3[Growth] * Table3[volume]
            );
            SUM ( Table3[volume] )
        );
        CALCULATETABLE (
            VALUES ( Table2[ID] );
            Table2[Category] = _ItemCategory;
            ALL ( Table2 )
        )
    )

Code formatted with  

[badger123]

@AlB Awesome! Thank you so much! 🙂 

[badger123]

Thanks for the help @AlB , although it's still not working in the way I want it to. I have tried your suggested measures and MaxBenchmark simply returns the summed volume of the selected item. I think this is due to the first measure maybe? Do you have any thoughts? 

[AlB · Accepted solution]

@badger123

I guess you've done stg different from what I described. It's working on my end. Have a look at this file.

[badger123]

@AlB you're right! It was the relationships I had created that were causing the issues. You're measures work perfectly and I have now fixed my relationships so it works in my context too. Thanks so much 🙂 

[badger123]

Hi @AlB

Thanks for getting back to me. I have included tables below. I'm actually looking to use these measures in the Gauge visual. 

ID	ID2	Country	Region	Volume
1	1C	UK	UKC	100
1	1D	UK	UKD	200
1	1E	UK	UKE	50
2	2C	UK	UKC	500
2	2D	UK	UKD	300
2	2E	UK	UKE	50
3	3C	UK	UKC	1000
3	3D	UK	UKD	800
3	3E	UK	UKE	500
4	4C	UK	UKC	100
4	4D	UK	UKD	200
4	4E	UK	UKE	100

ID	Item	Category
1	Item 1	Cat A
2	Item 2	Cat B
3	Item 3	Cat A
4	Item 4	Cat B
5	Item 5	Cat C
6	Item 6	Cat A