Calculating the accumulative by week

Anything related to PowerPivot and DAX Formuale
The maintenance guy
Posts: 3
Joined: Sat Nov 16, 2019 8:04 pm

Calculating the accumulative by week

Postby The maintenance guy » Sun Nov 24, 2019 11:58 pm

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.
Attachments
ROTATION 2.jpg
Correct result
ROTATION 2.jpg (57.1 KiB) Viewed 469 times
Rotation.jpg
incorrect result
Rotation.jpg (69.43 KiB) Viewed 469 times

Bernard Heymans
Posts: 28
Joined: Wed Feb 28, 2018 12:18 am

Re: Calculating the accumulative by week

Postby Bernard Heymans » Mon Nov 25, 2019 9:14 pm

Hi ,

Can you post a small set of data?
without example it is not easy to answer.

The maintenance guy
Posts: 3
Joined: Sat Nov 16, 2019 8:04 pm

Re: Calculating the accumulative by week

Postby The maintenance guy » Sun Dec 01, 2019 5:54 pm

Hello Bernard,

Here it is.

Mnay Thanks in Advance.
Attachments
sample.xlsx
(9.82 KiB) Downloaded 7 times

Bernard Heymans
Posts: 28
Joined: Wed Feb 28, 2018 12:18 am

Re: Calculating the accumulative by week

Postby Bernard Heymans » Mon Dec 02, 2019 11:45 pm

does this help?
Attachments
sample.xlsx
(432.26 KiB) Downloaded 3 times

The maintenance guy
Posts: 3
Joined: Sat Nov 16, 2019 8:04 pm

Re: Calculating the accumulative by week

Postby The maintenance guy » Fri Dec 06, 2019 6:07 am

Thank you, but unfortunately not


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests

cron