Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
fsim
Responsive Resident
Responsive Resident

calculate a grandtotal with filters on two tables

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

1 ACCEPTED SOLUTION
fsim
Responsive Resident
Responsive Resident

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")

 

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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:

8.png

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
fsim
Responsive Resident
Responsive Resident

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")

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.