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
rmcvicar
Helper I
Helper I

how to create Measure to sum column by multiple column by day

I have a dataset with a minutes column representing the number of minutes an event lasted entered for an Operator ("O") or for Mechanical ("M").  Each entry in the dataset has a date for the date of the entry of the event.  And the event is either on Shift 1, Shift 2 or Shift 3.

I want to sum minues by day, shift and entry type (Operator or Mechanical).

Is this a CALCULATE(SUM( WITH FILTER or Mulitipel FILTERS?

Date Time Minutes Shift Mech or Op
10/19/2020 07:15:00 52 3 M
10/19/2020 07:15:00 10 2 O
10/19/2020 07:15:00 44 3 O
10/19/2020 07:25:00 13 2 M
10/19/2020 08:04:00 83 2 O
10/19/2020 08:09:00 5 3 M
10/19/2020 08:40:00 12 3 M
10/19/2020 09:42:00 13 2 O
10/19/2020 10:20:00 16 3 O
10/19/2020 13:45:00 22 3 O
10/19/2020 14:07:00 32 3 M
10/19/2020 14:39:00 31 3 M
10/19/2020 14:57:00 207 2 O
10/19/2020 15:00:00 21 2 O
10/19/2020 15:00:00 30 3 O
10/19/2020 15:55:00 30 3 M
10/19/2020 16:08:00 158 3 O
10/19/2020 17:11:00 12 3 O
10/19/2020 19:00:00 10 3 M
10/19/2020 20:12:00 44 3 M
10/19/2020 21:55:00 20 3 O

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @rmcvicar ,

 

You can try to create a measure or calculated column with the following formula.

Column = CALCULATE(SUM('Table'[Minutes ]),ALLEXCEPT('Table','Table'[Date ],'Table'[Shift],'Table'[Mech or Op]))
Measure = CALCULATE(SUM('Table'[Minutes ]),ALLEXCEPT('Table','Table'[Date ],'Table'[Shift],'Table'[Mech or Op]))

6.png7.png

 

 

Reference: https://docs.microsoft.com/en-us/dax/allexcept-function-dax

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-stephen-msft
Community Support
Community Support

Hi @rmcvicar ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.


Best Regards,
Stephen Tao

resolved thank you

FrankAT
Community Champion
Community Champion

Hi @rmcvicar 

  1. I seperated in Power Query Date Time column in two different columns, Date and Time.
  2. With a matrix visual I get the following solution with your data sample:

22-10-_2020_12-15-11.png

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

v-stephen-msft
Community Support
Community Support

Hi @rmcvicar ,

 

You can try to create a measure or calculated column with the following formula.

Column = CALCULATE(SUM('Table'[Minutes ]),ALLEXCEPT('Table','Table'[Date ],'Table'[Shift],'Table'[Mech or Op]))
Measure = CALCULATE(SUM('Table'[Minutes ]),ALLEXCEPT('Table','Table'[Date ],'Table'[Shift],'Table'[Mech or Op]))

6.png7.png

 

 

Reference: https://docs.microsoft.com/en-us/dax/allexcept-function-dax

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.