Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
This is driving me crazy and is probably something simple that I'm overlooking..
Trying to create a calculated column that counts the distinct # of employees to date.
Curent Count Cumulative Count
Jan - 2 Employees 2
Feb - 2 Employees 4
Mar - 2 Employees 6
For some reason the cumulative numbers aren't working out.
Calculated Formula:
Cumulative =
CALCULATE (
DISTINCTCOUNT( [Employee] ),
FILTER (
ALL('YTD Attrition'),
[Term Date] <= MAX ( [Term Date] )
)
)
Here's what it looks like:
Thanks in advance!
@PowerBIzNess,
You can download my PBIX file to test.
Regards,
Lydia
@PowerBIzNess,
You can use the following DAX to calculate cumulative. Make sure that you choose "Don't summarize" for cumulative column in table visual.
Cumulative = CALCULATE(DISTINCTCOUNT('YTD Attrition'[Employee]),ALL('YTD Attrition'),'YTD Attrition'[Term Date]<=EARLIER('YTD Attrition'[Term Date]))
Regards,
Lydia
thanks so much for the reply. I realized my issue was the summarization in the visual, which displayed correctly when I selected 'Average'. For some reason the 'Don't Summarize' option doesn't appear as an option - I think this is because in order to gather some additional metrics I unpivoted a list of columns detailing attrition reasons, which created multiple rows for the same individuals. So for example my data looks like:
John Doe 5/1/2017 Compensation
John Doe 5/1/2017 Relocation
John Doe 5/1/2017 Benefits
Any suggestions you have on improving this would be appreciated!
@PowerBIzNess,
The DAX also works in your scenario, please check the following screenshot.
Regards,
Lydia
Thanks Lydia
What is your default summarization for the column? I don't have a 'Don't Summarize' option so I'm left having to average the numbers.
Try replacing "MAX" with "EARLIER"
Thanks for the reply. That made the number's a little less crazy but something is still throwing it off..
Please could you share your file via googledrive or onedrive or copy paste dataset
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |