Forum Discussion
Max by category
Hi guys,
I have the data set below which has duplicated Cost center headcount records due to multiple Account Type.
Can you advise which Dax formula I can use to get to the desired outcome column (screenshote below) based on the categories in peach column (By Country, By Cost Center, By Month)?
Editable version
| Country | Cost Center | Month | Account | Headcount by cost center | Desired outcome |
| Australia | A | Jan | payroll | 1 | 1 |
| Australia | A | Jan | bonus | 1 | |
| Australia | A | Jan | Overtime | 1 | |
| Australia | B | Jan | Overtime | 2 | 2 |
| Australia | B | Jan | bonus | 2 | |
| Australia | B | Jan | Payroll | 2 | |
| Australia | A | Feb | bonus | 4 | 4 |
| Australia | A | Feb | Payroll | 4 | |
| Australia | A | Feb | Overtime | 4 |
Nethergate , You need to add an index column in the power query and then try this DAX new column
var _min = minx(filter(Table, [Country] =earlier([Country]) && [Cost Center] =earlier([cost Center]) && [Month] =earlier([Month])), [Index])
var _max = maxx(filter(Table, [Country] =earlier([Country]) && [Cost Center] =earlier([cost Center]) && [Month] =earlier([Month])), [headcount])
return
if([Index] =_min, _max, blank())
3 Replies
Nethergate , Is headcount is measure or column ?
It's a column
Nethergate , You need to add an index column in the power query and then try this DAX new column
var _min = minx(filter(Table, [Country] =earlier([Country]) && [Cost Center] =earlier([cost Center]) && [Month] =earlier([Month])), [Index])
var _max = maxx(filter(Table, [Country] =earlier([Country]) && [Cost Center] =earlier([cost Center]) && [Month] =earlier([Month])), [headcount])
return
if([Index] =_min, _max, blank())
