Hello,
I have a measure that calculates how many times a worker was "shifted/Rotated" from one production line to another per week, but the results given to me are only correct if i chose to see the results by 1 week and not more, if i choose 2 weeks it runs the measure on the results of 2 weeks together so i get a wrong answer, what i need to have is a measure that adds up the "Rotation" result of each week to the other chosen week and so on!
Here is the measure:
Rotation = DISTINCTCOUNT('Table 1'[name of the production line])
/
VALUE(DISTINCTCOUNT('Table 1'[Worker name]))
As you can see the measure depends on the function "DISTINCTCOUNT" , and that is because lets say that worker "Mark" worked on 3 different production lines this week "Production lines 8,3,5,8" -yes they are 4 but two of them are the same "Line number8" so its not counted as a "Rotation"- so it means that he was rotated 3 times only this week, so the result of this measure above for this specific week well be "3" till now all is fine, so let´s assume that for the next week he was "Rotated" 4 times for lines(8,5,3,7) so the results should be 7 times Rotation if i choose showing me the results for those 2 weeks, but what happens is the function "DISTINCOUNT" well be running again on the total results of the 2 weeks excluding any repeated production line name showing me the final result as 2 "because lines 8,3,5 will be counted only once in the 2 weeks since i am using DISTINCOUNT", if i use "COUNT" it will only counts the number of production lines without eliminating the "repeated names".
Would appreciate your help on this.
Many Thanks in Advance.
Calculating the accumulative by week
-
- Posts: 3
- Joined: Sat Nov 16, 2019 8:04 pm
Calculating the accumulative by week
- Attachments
-
- Correct result
- ROTATION 2.jpg (57.1 KiB) Viewed 466 times
-
- incorrect result
- Rotation.jpg (69.43 KiB) Viewed 466 times
-
- Posts: 28
- Joined: Wed Feb 28, 2018 12:18 am
Re: Calculating the accumulative by week
Hi ,
Can you post a small set of data?
without example it is not easy to answer.
Can you post a small set of data?
without example it is not easy to answer.
-
- Posts: 3
- Joined: Sat Nov 16, 2019 8:04 pm
Re: Calculating the accumulative by week
Hello Bernard,
Here it is.
Mnay Thanks in Advance.
Here it is.
Mnay Thanks in Advance.
- Attachments
-
- sample.xlsx
- (9.82 KiB) Downloaded 7 times
-
- Posts: 28
- Joined: Wed Feb 28, 2018 12:18 am
Re: Calculating the accumulative by week
does this help?
- Attachments
-
- sample.xlsx
- (432.26 KiB) Downloaded 3 times
-
- Posts: 3
- Joined: Sat Nov 16, 2019 8:04 pm
Re: Calculating the accumulative by week
Thank you, but unfortunately not
Who is online
Users browsing this forum: No registered users and 2 guests