Dear experts,
unfortunately I am struggling with a problem, I have used the following link as a guide:
https://community.fabric.microsoft.com/t5/Desktop/Dax-for-sum-of-distinct-values/td-p/1446048
I have a large dataset that is structured in the following way:
| Work center | Plant | Year | Period&Year | Machine time (h) | Working Days | Code |
| 1 | 110 | 2022 | 01.2022 | 10 | 20 | 1105 |
| 1 | 110 | 2022 | 01.2022 | 5 | 20 | 1205 |
| 2 | 110 | 2022 | 01.2022 | 15 | 20 | 1105 |
| 3 | 110 | 2022 | 01.2022 | 20 | 20 | 1205 |
| 4 | 150 | 2022 | 01.2022 | 40 | 20 | 1105 |
| 5 | 150 | 2022 | 01.2022 | 50 | 20 | 1200 |
| 6 | 150 | 2022 | 01.2022 | 60 | 20 | 1105 |
| all | 110 | 2021 | 01.2021 | 400 | 21 | all |
| all | 1110 | 2021 | 02.2021 | 500 | 19 | all |
Table excerpt for 2022 with working days since this is my main issue:
| Work center | Plant | Year | Period&Year | Machine time (h) | Working Days | Code |
| all | 110 | 2022 | 01.2022 | 400.000 | 21 | all |
| all | 110 | 2022 | 02.2022 | 405.000 | 20 | all |
| all | 110 | 2022 | 03.2022 | 395.000 | 23 | all |
| all | 110 | 2022 | 04.2022 | 375.000 | 19 | all |
| all | 110 | 2022 | 05.2022 | 360.000 | 21 | all |
| all | 110 | 2022 | 06.2022 | 450.000 | 20 | all |
| all | 110 | 2022 | 07.2022 | 450.000 | 21 | all |
| all | 110 | 2022 | 08.2022 | 440.000 | 23 | all |
| all | 110 | 2022 | 09.2022 | 430.000 | 22 | all |
| all | 110 | 2022 | 10.2022 | 420.000 | 22 | all |
| all | 110 | 2022 | 11.2022 | 410.000 | 21 | all |
| all | 110 | 2022 | 12.2022 | 400.000 | 21 | all |
| Total | 110 | 2022 | all | 4.935.000 | 252 | all |
The dataset is growing each month and includes data from 2016 onwards in this structure. There is no hierarchy for year & year.period for drilldown, but since the data is maintained in the rows as listed above I am not expecting an issue.
I want to achive the following: Calcuate the number of shifts per plant, per each work center & also considering the codes.
But this is also my issue since it's essentially counting the working times x times (due to several codes per work center per period).
My DAX formula:
SUMX TEST V3 =
SUMX(SUMMARIZE(Table','Period.Year,'Plant),MAX([Working days]))
My issue:
When I insert my measure on the level of Period.Year for each plant, it returns the correct number per period. There is 1 column per period, so 12 in total with the correct values each = sum 252
When I insert my measure on the level of Year however, it somehow returns a total of 276. I have been looking for the issue for 3 hours now and just can't find it.. The delta is also 24, which doesn't exist as a working day entry in my dataset for 2022...
Thanks for your help, I hope my description is clear enough.
