Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
There is something I don't understand in my formula. I'm sure one can help.
My data are in two linked table. One, the Tallysheet, contains (among many not used fields)an activity_id, a date,a duration and a location. The second table contains the kind of activities.
example of my TallySheet:
id date duration location
01 2018-08-26 20 room1
02 2018-08-26 10 room2
03 2018-08-26 30 room2
04 2018-08-26 5 room1
and in my activities table
id activity
01 production
02 cleaning
03 production
04 unexpected stop
my goal is to calculate the % of the time the rooms were in production time during the selected period of time (with a slider)
something like
room prod prod% total running time
room1 20 80 25
room2 30 75 40
I don't care (yet) about the other kinds of activites so I filtered them out.
My main problem is to get the grandtotal with all the activities but still filtered by date and room.
I did this
GrandTotal = calculate(sumx(tallysheet,[duration]), all(activities))
room prod GrandTotal
room1 20 65
room2 30 65
why did I lose the other filters ?
thank you in advance !
Fred
Solved! Go to Solution.
Hi Lin,
thank you for your suggestion !
It almost did the job. The measure needs to keep the date filtering.
I found A way to get my results this way: I hard coded the location filter and let DAX to do all the others filtering.
ErrorDuration (min) = CALCULATE(sum(tallySheets[duration]);activities[label (groups)]="PANNE")
and
error% = DIVIDE(ErrorDuration (min)];sum(tallySheets[duration]);"na")
Hi@ fsim
After my research , you can try to use these two measure
GrandTotal = calculate(sumx(tallysheet,[duration]),ALLEXCEPT(TallySheet,TallySheet[location])) Pro% = DIVIDE(CALCULATE(SUM(TallySheet[duration])),[GrandTotal])
Result:
Best Regards,
Lin
Hi Lin,
thank you for your suggestion !
It almost did the job. The measure needs to keep the date filtering.
I found A way to get my results this way: I hard coded the location filter and let DAX to do all the others filtering.
ErrorDuration (min) = CALCULATE(sum(tallySheets[duration]);activities[label (groups)]="PANNE")
and
error% = DIVIDE(ErrorDuration (min)];sum(tallySheets[duration]);"na")
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |