cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
don_writer Regular Visitor
Regular Visitor

sum of max values across multiple slicers

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:

 

Capturedata.JPG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
don_writer Regular Visitor
Regular Visitor

Re: sum of max values across multiple slicers

Okay, I think I solved this:

 

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

View solution in original post

4 REPLIES 4
Microsoft v-danhe-msft
Microsoft

Re: sum of max values across multiple slicers

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
don_writer Regular Visitor
Regular Visitor

Re: sum of max values across multiple slicers

Here are the results I expect. 

 

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

Capture2018OP.JPG

don_writer Regular Visitor
Regular Visitor

Re: sum of max values across multiple slicers

Okay, I think I solved this:

 

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

View solution in original post

Microsoft v-danhe-msft
Microsoft

Re: sum of max values across multiple slicers

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors