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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Sum Dax help

Hi all,

 

Here is scenario wherein we have to calculate the sum based on two condition of the same column and then multiple with the sum of another column and eventually add up the total for a particular quarter i.e. 

 

YearQuarterVesselCodeBus NameStop PointDateHrsLoad
2018Q1TAAmberStart Passage3/15/18 12:00 AM1230
2018Q1TAAmbera3/16/18 12:00 AM120
2018Q1TAAmberEnd passage3/20/18 12:00 AM140
2018Q1TAAmberHalt3/25/18 12:00 AM322000
2018Q1TAAmberStart Passage3/27/18 12:00 AM230
2018Q1TAAmbere3/28/18 12:00 AM560
2018Q2TAAmberf4/2/18 12:00 AM670
2018Q2TAAmberg4/3/18 12:00 AM890
2018Q2TAAmberEnd passage4/4/18 12:00 AM10
2018Q2TAAmberHalt4/5/18 3:00 AM142000
2018Q2TAAmberStart Passage4/5/18 5:00 AM250
2018Q2TAAmbera6/22/18 12:00 AM340
2018Q2TAAmberEnd passage6/24/18 12:00 AM310
2018Q2TAAmberHalt6/26/18 12:00 AM123000
2018Q2TBRadianStart Passage6/26/18 3:00 AM760
2018Q2TBRadiana6/28/18 12:00 AM850
2018Q3TBRadianb7/14/18 12:00 AM64 
2018Q3TBRadianEnd passage7/18/18 12:00 AM540
2018Q3TBRadianHalt7/22/18 12:00 AM512000
2018Q3TBRadianStart Passage7/22/18 6:00 AM350
2018Q4TBRadiant11/29/18 12:00 AM450
2018Q4TBRadianEnd passage12/24/18 12:00 AM130
2018Q4TBRadianHalt12/25/18 12:00 AM1250
2018Q4TBRadianStart Passage12/25/18 12:00 AM20
2019Q1TBRadianon road1/1/19 12:00 AM140
2019Q1TBRadianOn road1/3/19 12:00 AM150

 

 

 

 

 

 

 

For 1st section the hours total would be 181 and load for that travel would be 2000. The total work is 181 * 2000 =362000. in similar fashion for each start and stop point for each bus

 

 

Now we have to calculate the sum for each start and till halt stop point for each bus and same goes for each cargo. then we would need to multiply the sum of hrs and some of cargo for that start till halt point for that voyage

2 ACCEPTED SOLUTIONS
v-cherch-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous

 

You may try to add a group column and then you may get the measures.Please check the attached file.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
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

So, for something like this you are going to need some kind of identifier for "grouping" to occur. For example, your first 3 rows should be 1 and then your next 6 rows should be 2, etc. because they all belong together. You might find my work on Cthulhu of use. 

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Hi all,

 

Here is scenario wherein we have to calculate the sum based on two condition of the same column and then multiple with the sum of another column and eventually add up the total for a particular quarter i.e. 

 

YearQuarterVesselCodeBus NameStop PointDateHrsLoad
2018Q1TAAmberStart Passage3/15/18 12:00 AM1230
2018Q1TAAmbera3/16/18 12:00 AM120
2018Q1TAAmberEnd passage3/20/18 12:00 AM140
2018Q1TAAmberHalt3/25/18 12:00 AM322000
2018Q1TAAmberStart Passage3/27/18 12:00 AM230
2018Q1TAAmbere3/28/18 12:00 AM560
2018Q2TAAmberf4/2/18 12:00 AM670
2018Q2TAAmberg4/3/18 12:00 AM890
2018Q2TAAmberEnd passage4/4/18 12:00 AM10
2018Q2TAAmberHalt4/5/18 3:00 AM142000
2018Q2TAAmberStart Passage4/5/18 5:00 AM250
2018Q2TAAmbera6/22/18 12:00 AM340
2018Q2TAAmberEnd passage6/24/18 12:00 AM310
2018Q2TAAmberHalt6/26/18 12:00 AM123000
2018Q2TBRadianStart Passage6/26/18 3:00 AM760
2018Q2TBRadiana6/28/18 12:00 AM850
2018Q3TBRadianb7/14/18 12:00 AM64 
2018Q3TBRadianEnd passage7/18/18 12:00 AM540
2018Q3TBRadianHalt7/22/18 12:00 AM512000
2018Q3TBRadianStart Passage7/22/18 6:00 AM350
2018Q4TBRadiant11/29/18 12:00 AM450
2018Q4TBRadianEnd passage12/24/18 12:00 AM130
2018Q4TBRadianHalt12/25/18 12:00 AM1250
2018Q4TBRadianStart Passage12/25/18 12:00 AM20
2019Q1TBRadianon road1/1/19 12:00 AM140
2019Q1TBRadianOn road1/3/19 12:00 AM150

 

 

 

 

 

 

 

For 1st section the hours total would be 181 and load for that travel would be 2000. The total work is 181 * 2000 =362000. in similar fashion for each start and stop point for each bus

 

 

Now we have to calculate the sum for each start and till halt stop point for each bus and same goes for each cargo. then we would need to multiply the sum of hrs and some of cargo for that start till halt point for that voyage

Anonymous
Not applicable

Hi all,

 

Here is scenario wherein we have to calculate the sum based on two condition of the same column and then multiple with the sum of another column and eventually add up the total for a particular quarter i.e. 

 

YearQuarterVesselCodeBus NameStop PointDateHrsLoad
2018Q1TAAmberStart Passage3/15/18 12:00 AM1230
2018Q1TAAmbera3/16/18 12:00 AM120
2018Q1TAAmberEnd passage3/20/18 12:00 AM140
2018Q1TAAmberHalt3/25/18 12:00 AM322000
2018Q1TAAmberStart Passage3/27/18 12:00 AM230
2018Q1TAAmbere3/28/18 12:00 AM560
2018Q2TAAmberf4/2/18 12:00 AM670
2018Q2TAAmberg4/3/18 12:00 AM890
2018Q2TAAmberEnd passage4/4/18 12:00 AM10
2018Q2TAAmberHalt4/5/18 3:00 AM142000
2018Q2TAAmberStart Passage4/5/18 5:00 AM250
2018Q2TAAmbera6/22/18 12:00 AM340
2018Q2TAAmberEnd passage6/24/18 12:00 AM310
2018Q2TAAmberHalt6/26/18 12:00 AM123000
2018Q2TBRadianStart Passage6/26/18 3:00 AM760
2018Q2TBRadiana6/28/18 12:00 AM850
2018Q3TBRadianb7/14/18 12:00 AM64 
2018Q3TBRadianEnd passage7/18/18 12:00 AM540
2018Q3TBRadianHalt7/22/18 12:00 AM512000
2018Q3TBRadianStart Passage7/22/18 6:00 AM350
2018Q4TBRadiant11/29/18 12:00 AM450
2018Q4TBRadianEnd passage12/24/18 12:00 AM130
2018Q4TBRadianHalt12/25/18 12:00 AM1250
2018Q4TBRadianStart Passage12/25/18 12:00 AM20
2019Q1TBRadianon road1/1/19 12:00 AM140
2019Q1TBRadianOn road1/3/19 12:00 AM150

 

 

 

 

 

 

 

For 1st section the hours total would be 181 and load for that travel would be 2000. The total work is 181 * 2000 =362000. in similar fashion for each start and stop point for each bus

 

 

Now we have to calculate the sum for each start and till halt stop point for each bus and same goes for each cargo. then we would need to multiply the sum of hrs and some of cargo for that start till halt point for that voyage

So, for something like this you are going to need some kind of identifier for "grouping" to occur. For example, your first 3 rows should be 1 and then your next 6 rows should be 2, etc. because they all belong together. You might find my work on Cthulhu of use. 

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler- You are awesome , thanks so much for the help

v-cherch-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous

 

You may try to add a group column and then you may get the measures.Please check the attached file.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-cherch-msft  - Thanks so much for the help u guys are super cool , this community is very very helpful

Anonymous
Not applicable

Thanks so much for the help u guys are super cool , this community is very very helpful:)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.