Sum of Maximum Values

Anything related to PowerPivot and DAX Formuale
monkeymagik
Posts: 3
Joined: Sun Sep 22, 2019 2:49 pm

Sum of Maximum Values

Postby monkeymagik » Sun Sep 22, 2019 3:26 pm

I have an employee list that in Excel I am trying to create some pivot tables for.

It has a row for each employee, columns for stuff like manager, location, function, role plus one column for each month for the next few years. The month columns have a value from 0 to 1 depending on whether the employee is full-time or not.

Role ID Role Title Function Location Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19
1 Role 1 Function 1 Location 1 1 1 1 1 1 1 1 1 1 1 1 1
2 Role 1 Function 1 Location 2 1 1 1 1 1 1 1 1 1 1 1 1
3 Role 1 Function 1 Location 1 1 1 1 1 1 1 1 1 1 1 1 1
4 Role 1 Function 1 Location 2 1 1 1 1 1 1 1 1 1 1 1 1
5 Role 1 Function 1 Location 1 1 1 1 0 0 0 0 0 0 0 0 0
6 Role 2 Function 1 Location 2 0 0 0 0 0 1 1 1 1 0 0 0
7 Role 2 Function 1 Location 1 0 0 0 0 0 1 1 0 0 0 0 0
8 Role 2 Function 1 Location 2 0 0 0 0 0 0 0 0 0 0 1 1
9 Role 2 Function 1 Location 1 0 0 0 0 0 0 0 0 0 1 1 1
10 Role 2 Function 1 Location 2 0 0 0 0 0 0 0 1 1 1 1 1
11 Role 3 Function 1 Location 1 0 1 1 1 1 1 1 1 1 1 1 1
12 Role 4 Function 1 Location 2 0 1 1 1 1 1 1 1 1 1 1 1
13 Role 5 Function 1 Location 1 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5

I can create a basic pivot table using the months as the columns, but I'd like to do it with the data model to get more flexibility.

I've reformatted the data with PowerQuery and loaded into the data model so there is a month and value column
1 Role 1 Function 1 Location 1 1/01/2019 1
1 Role 1 Function 1 Location 1 1/02/2019 1
1 Role 1 Function 1 Location 1 1/03/2019 1
1 Role 1 Function 1 Location 1 1/04/2019 1
1 Role 1 Function 1 Location 1 1/05/2019 1
1 Role 1 Function 1 Location 1 1/06/2019 1
1 Role 1 Function 1 Location 1 1/07/2019 1
1 Role 1 Function 1 Location 1 1/08/2019 1
1 Role 1 Function 1 Location 1 1/09/2019 1
1 Role 1 Function 1 Location 1 1/10/2019 1
...etc

I've created a measure to use for values:
[DAX]=SUMX(VALUES(Table1[Role ID]), CALCULATE(MAX(Table1[FTE])))/DAX]

This works fine for months, but when the months are grouped by quarter or year, they are not appearing to add up correctly. I would expect to see the maximum month value to show.

MattAllington
Posts: 1062
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Sum of Maximum Values

Postby MattAllington » Sun Sep 22, 2019 6:43 pm

Your un-pivoting is good - well done. You really should now build a star schema. This makes the dax easier to write and the model more efficient. It looks like you need an employee table (lookup table) with the role, id, function, location etc. Just leave the role ID in the data table. Then you need a calendar table.

Read these articles - they will help you
https://exceleratorbi.com.au/the-optima ... ivot-data/
https://exceleratorbi.com.au/power-pivo ... ar-tables/

Once you have done this, I think your dax just needs to be
MAX(Table1[FTE])

Although I don't really know what your formula is supposed to do.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

monkeymagik
Posts: 3
Joined: Sun Sep 22, 2019 2:49 pm

Re: Sum of Maximum Values

Postby monkeymagik » Tue Sep 24, 2019 12:33 am

Thanks for the helpful answer, it's gotten me part of the way there... seems cleaner at least. One issue I have is the data I have is what it is.

What I'm after is the sum of all the roles that are filled in a particular month, but if I group by quarter or year, I want the value of the maximum month total to show. i.e. if the head count for a particular department was 5 in January, 7 in Feb and 7 in March, I'd like the quarter figure to show 7 not 19. Same applies for year.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests