Log In
Register
Home
August 23, 2021
Offline
Hello everyone! I'm a little bit stuck on a headcount calculation that I need for a client project I'm working on. I've watched the HR dashboard video (which is excellent!) but I can't use the function of "count" on employee IDs because the data coming to me is manually entered by reps in various locations. So instead of a unique identifier for each person, Location A has 250 in January, 265 in February, and 240 in March, for example. The measure that I currently have in place is:
=CALCULATE(SUM(Consolidated_Dashboard_Table_10[Actual Headcount]),FILTER(Consolidated_Dashboard_Table_10,[Formatted Date]=MAX([Formatted Date])))
It seems to work until I filter the data - some of my totals in my pivot table go away and I need help to fix that. Thank you!
See Answer
July 16, 2010
Offline
Hi Mamie,
Welcome to our forum! We'd need to see your file or a mockup of your file, or at a minimum, screenshots so we can understand the filter context you're wanting to apply.
Mynda
August 23, 2021
Offline
Hi Mynda,
Here is what my Pivot table is looking like with no filters applied:
When I filter the data, the numbers for the month are there, but the subtotal for the year is gone. I got a little closer when I redid the measure to be the following (the totals are still present) but it's totaling everything instead of just giving me the most recent total based on my filter choices (location, business unit, etc.).
=CALCULATE(SUM(Consolidated_Dashboard_Table_10[Actual Headcount]),FILTER(ALL(Consolidated_Dashboard_Table_10[Formatted Date]),LASTDATE(Consolidated_Dashboard_Table_10[Formatted Date])))
August 23, 2021
Offline
Hi Mynda,
Here is my note with more details.
https://privnote.com/RI5wdC6Y#AlHJTuUxQ
I think the issue is in the FILTER part of my formula, I just can't figure out which part.
I appreciate your help!
Mamie
July 16, 2010
Offline
Hi Mamie,
I couldn't open the privnote and the screenshots don't tell the story enough for me to help. Please create a dummy/mockup file containing some unsensitive data. It doesn't need to be a lot of data. Just enough to illustrate the issue and represent all possible scenarios.
Thanks,
Mynda
August 23, 2021
Offline
Hi Mynda,
Aw that's too bad! Maybe it was user error on my part...
I wasn't sure what the best way to scale down the file without throwing lots of things off so I relabeled everything to be generic. I just tried to load the dummy file I made and it's too big to post here. 🙁
Here is the link to the Dropbox folder if that's helpful: https://www.dropbox.com/sh/sma.....iP0Ha?dl=0
Thanks!
Mamie
July 16, 2010
Offline
Hi Mamie,
Thanks for sharing your file. You can use the implicit measures already built into PivotTables for these calculations. That is, build a PivotTable with the following fields:
Rows:
Formatted Date (grouped by Year and Month)
Employee Type
Values:
Actual Headcount
Budget Approved Headcount
Columns:
Values
Right-click on the month name in the PivotTable > select Subtotal by Month. Repeat for the Years field.
Hope that points you in the right direction.
Mynda
August 23, 2021
Offline
Hi Mynda,
Thanks for your reply! I hadn't even thought about using the Formatted Date because I just recently added that.
I get really close with what you outline above, but I'm still stuck on getting the Year total to show me the most recent Month total where the headcount is greater than 0. There are some locations that will not have data for the most recent date in the workbook but I still need to show their most recent headcount. How can I do this?
I appreciate your help! 🙂
Power Query
Power Pivot
Power BI
October 17, 2018
Offline
In the Power Pivot course, it was suggested to try to use Explicit Measures rather than Implicit Measures.
Does Mamie's question lend itself more to an Implicit Measure? If that's the case I'm not quite sure when best to use Explicit vs Implicit Measure. Is this just based on experience with Power Pivot?
Thanks,
Steve
August 23, 2021
Offline
Hi Mynda,
I went back to your HR Dashboard video and I seem to have gotten this to work by writing another measure based on what you wrote for separations in the HR Dashboard video as follows:
=CALCULATE([Actual Headcount Calc],FILTER(Consolidated_Dashboard_Table_10,NOT(ISBLANK([Actual Headcount Calc]))))
This is now giving me the most recent month total as a subtotal for the year even if the max date in the table has no data. For example, the most recent date in my file is July 2021 but my biggest location problem child didn't have any data for July - their most recent data was June. Now the June total is showing in the 2021 subtotal instead of a blank (or 0), which is exactly what I wanted. 🙂 🙂 I'm so happy right now!
Answers Post
July 16, 2010
Offline
@Mamie, I see. I misunderstood your question. Glad you got it working.
@Steve, explicit measures are ideal when you need to write further DAX measures based on what would normally be an implicit measure e.g. something basic like a SUM or COUNT etc. But when you only need something basic like a SUM or COUNT, which it what I thought Mamie needed, then there's no need to over engineer it with explicit measures. Hope that makes sense.
1 Guest(s)
