Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |