sum of max values across multiple slicers

[don_writer]

I have seen several topic that are similar but just don't quite accomplish what I am looking for.

Here is some sample_data

Basically I have a table with Month (assume these are all months in a particular year. Lets say 2017), auto_req_ID, headcount (this is headcount within the reqID), Division, and Company. Lets call this table OPdata1.

A auto_req_ID (or req) can be open for several months. If I had ten reqs open in a month I'd add them together to get a total in the month (sum(OPdata1[headcount]). Then it would sum the headcount when I slicer for that month. However, if I was to select a year it would add all the headcounts month to month. I don't want to do that. I want to add the req headcount once over the course of the year no matter how many months it stays open. And sometimes the headcount would go up and down. So in March 2018 its opened with a headcount of 7, April it has 15, and May it has 12. In the annual slicer for 2018 it should count that req as 15. I did this by 

Annual Open Positions = CALCULATE(
    MAXX(OPdata1,OPdata1[headcount])
    )

Great! Yay!

But then I want to slice this up via Division and Company. So I see it takes the Max headcount within that division over the year, only one. Similarly, it will only take the max value within the company. 

So this is what I want. I would like to slice this data five ways - month, quarter, year, Division, and Company.

I want it to sum headcount but only for the reqID with the highest value month to month across the quarter or the year. And I want it to sum all reqs within a Division then within a company (but only choose one version of the req ID that has the highest headcount).

here is an example from the sample data:

auto_req_ID 188BR had a headcount of 9 in April, 7 in February, and 12 in March (sorry the months aren't tied to MonthNo). When counting that req it should be included in the annual numbers as 12.

I feel like I am on the right track but not nesting my sumx and max and calculates appropriately. Your help is very appreciated.

Best regards,

~Don

[v-danhe-msft]

Hi @don_writer ,

I have asked for your access(danielhe1234567@gmail.com), and could you please post your desired result if possible that I could give your a better suggestion.

Regards,

Daniel He

[don_writer]

Here are the results I expect. 

Only the highest headcount across all months should be counted for the RedID in that Company/Division.

[don_writer · Accepted solution]

Okay, I think I solved this:

AnnualRecID = 
SUMX (
    SUMMARIZE ( OPdata1, OPdata1[auto_req_id], OPdata1[Division], "maxHeadcount", MAX ( OPdata1[headcount]) ),
    [maxHeadcount]
)

[v-danhe-msft]

Hi @don_writer ,

It's pleasant that your problem has been solved, could you please mark one reply as Answered to close this topic?

Regards,

Daniel He