cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
v-danhe-msft Super Contributor
Super Contributor

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

v-danhe-msft Super Contributor
Super Contributor

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 398 members 3,248 guests
Please welcome our newest community members: