Max Value group by or filter by other columns

[MichaelaMul]

Hi everyone!

I am trying to find the how many times Brand A has the most volume share in its category in each geography. In Power Query I separated Brand A from the other Products. Now I am trying to make a measure or a new column that calculates the following:

If Brand A Volume Share = Other Volume Share, Count 1, if not null. Or I would want to get the Max of the Other Volume Share of Segment by Category (excluding the overall volume share of 100%)

How would I do that? 

Sample data below: In blue is what I would like:

Geography	Product	Overall Category	Category	Volume Share of Segment	Brand A Volume Share of Segment	Other Volume Share of Segment	MAX OF Other Volume Share of Segment	Count where Brand A is the Max
Geo A	Product A Almond	Almond	Almond	20	20	20
Geo A	Product B Almond	Almond	Almond	50		50	50
Geo A	Product C Almond	Almond	Almond	30		30
Geo A	Overall Almond	Almond		100
Geo B	Product A Almond	Almond	Almond	60	60	60	60	1
Geo B	Product B Almond	Almond	Almond	30		30
Geo B	Product C Almond	Almond	Almond	10		10
Geo B	Overall Almond	Almond		100
Geo A	Product A Oat	Oat	Oat	50	50	60	60	1
Geo A	Product B Oat	Oat	Oat	30		30
Geo A	Product C Oat	Oat	Oat	20		20
Geo A	Overall Oat	Oat		100
Geo B	Product A Oat	Oat	Oat	60	60	60	60	1
Geo B	Product B Oat	Oat	Oat	25		25
Geo B	Product C Oat	Oat	Oat	15		15
Geo B	Overall Oat	Oat		100

[jgeddes · Accepted solution]

You can create two calculated columns 

Other Volume Share Max = 
var _max = 
CALCULATE(
        MAX([Other Volume Share of Segment]),
        ALLEXCEPT('Table', 'Table'[Geography], 'Table'[Category])
    )
var _result =
IF(
    _max = [Other Volume Share of Segment],
    _max,
    BLANK()
)
RETURN
_result

Count Brand A Max = 
var _max = 
CALCULATE(
        MAX([Volume Share of Segment]),
        ALLEXCEPT('Table', 'Table'[Geography], 'Table'[Category])
    )
var _result =
IF(
    AND([Brand A Volume Share of Segment] >= _max, [Category] <>""),
    1,
    0
)
RETURN
_result

And you will end up with...

[MichaelaMul]

Hi again! I am trying to make now a total count for this by Director and or Manager in a card view and it isn't working. Do you know what measure I would use, I think it would be sum( Count Brand A Max), if Directors name is Tom or Jerry.

Using the sample data with, the following table connected to it, by Geography.

Geography	Director	Manager	Desired Count
Geo A	Tom	Bill	1
Geo B	Tom	Bill	2
Geo C	Tom	Bob
Geo D	Tom	Nowell
Geo E	Jerry	Joe
Geo F	Jerry	Doe
Geo G	Jerry	Doe

[jgeddes]

You can try this code as a calculated column in the geography table...

Sum of Table Count Brand A Max = 
var _currentGeo =
geoTable[Geography]
var _result =
SUMX(
    FILTER('Table', 'Table'[Geography] = _currentGeo),
    'Table'[Count Brand A Max]
)
RETURN
_result

[MichaelaMul]

thank you so much! that worked!