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 PointDateHrsLoad2018Q1TAAmberStart Passage3/15/18 12:00 AM12302018Q1TAAmbera3/16/18 12:00 AM1202018Q1TAAmberEnd passage3/20/18 12:00 AM1402018Q1TAAmberHalt3/25/18 12:00 AM3220002018Q1TAAmberStart Passage3/27/18 12:00 AM2302018Q1TAAmbere3/28/18 12:00 AM5602018Q2TAAmberf4/2/18 12:00 AM6702018Q2TAAmberg4/3/18 12:00 AM8902018Q2TAAmberEnd passage4/4/18 12:00 AM102018Q2TAAmberHalt4/5/18 3:00 AM1420002018Q2TAAmberStart Passage4/5/18 5:00 AM2502018Q2TAAmbera6/22/18 12:00 AM3402018Q2TAAmberEnd passage6/24/18 12:00 AM3102018Q2TAAmberHalt6/26/18 12:00 AM1230002018Q2TBRadianStart Passage6/26/18 3:00 AM7602018Q2TBRadiana6/28/18 12:00 AM8502018Q3TBRadianb7/14/18 12:00 AM64 2018Q3TBRadianEnd passage7/18/18 12:00 AM5402018Q3TBRadianHalt7/22/18 12:00 AM5120002018Q3TBRadianStart Passage7/22/18 6:00 AM3502018Q4TBRadiant11/29/18 12:00 AM4502018Q4TBRadianEnd passage12/24/18 12:00 AM1302018Q4TBRadianHalt12/25/18 12:00 AM12502018Q4TBRadianStart Passage12/25/18 12:00 AM202019Q1TBRadianon road1/1/19 12:00 AM1402019Q1TBRadianOn 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

Anonymous

Not applicable

7 years ago

Solved

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.

Year	Quarter	VesselCode	Bus Name	Stop Point	Date	Hrs	Load
2018	Q1	TA	Amber	Start Passage	3/15/18 12:00 AM	123	0
2018	Q1	TA	Amber	a	3/16/18 12:00 AM	12	0
2018	Q1	TA	Amber	End passage	3/20/18 12:00 AM	14	0
2018	Q1	TA	Amber	Halt	3/25/18 12:00 AM	32	2000
2018	Q1	TA	Amber	Start Passage	3/27/18 12:00 AM	23	0
2018	Q1	TA	Amber	e	3/28/18 12:00 AM	56	0
2018	Q2	TA	Amber	f	4/2/18 12:00 AM	67	0
2018	Q2	TA	Amber	g	4/3/18 12:00 AM	89	0
2018	Q2	TA	Amber	End passage	4/4/18 12:00 AM	1	0
2018	Q2	TA	Amber	Halt	4/5/18 3:00 AM	14	2000
2018	Q2	TA	Amber	Start Passage	4/5/18 5:00 AM	25	0
2018	Q2	TA	Amber	a	6/22/18 12:00 AM	34	0
2018	Q2	TA	Amber	End passage	6/24/18 12:00 AM	31	0
2018	Q2	TA	Amber	Halt	6/26/18 12:00 AM	12	3000
2018	Q2	TB	Radian	Start Passage	6/26/18 3:00 AM	76	0
2018	Q2	TB	Radian	a	6/28/18 12:00 AM	85	0
2018	Q3	TB	Radian	b	7/14/18 12:00 AM	64
2018	Q3	TB	Radian	End passage	7/18/18 12:00 AM	54	0
2018	Q3	TB	Radian	Halt	7/22/18 12:00 AM	51	2000
2018	Q3	TB	Radian	Start Passage	7/22/18 6:00 AM	35	0
2018	Q4	TB	Radian	t	11/29/18 12:00 AM	45	0
2018	Q4	TB	Radian	End passage	12/24/18 12:00 AM	13	0
2018	Q4	TB	Radian	Halt	12/25/18 12:00 AM	12	50
2018	Q4	TB	Radian	Start Passage	12/25/18 12:00 AM	2	0
2019	Q1	TB	Radian	on road	1/1/19 12:00 AM	14	0
2019	Q1	TB	Radian	On road	1/3/19 12:00 AM	15	0

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

Greg_Deckler
7 years ago
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
v-cherch-msft
7 years ago
Hi Anonymous

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

Regards,

Cherie
Sum Dax help .pbix74 KB

7 Replies

Anonymous

Not applicable

7 years ago

Hi all,

Year	Quarter	VesselCode	Bus Name	Stop Point	Date	Hrs	Load
2018	Q1	TA	Amber	Start Passage	3/15/18 12:00 AM	123	0
2018	Q1	TA	Amber	a	3/16/18 12:00 AM	12	0
2018	Q1	TA	Amber	End passage	3/20/18 12:00 AM	14	0
2018	Q1	TA	Amber	Halt	3/25/18 12:00 AM	32	2000
2018	Q1	TA	Amber	Start Passage	3/27/18 12:00 AM	23	0
2018	Q1	TA	Amber	e	3/28/18 12:00 AM	56	0
2018	Q2	TA	Amber	f	4/2/18 12:00 AM	67	0
2018	Q2	TA	Amber	g	4/3/18 12:00 AM	89	0
2018	Q2	TA	Amber	End passage	4/4/18 12:00 AM	1	0
2018	Q2	TA	Amber	Halt	4/5/18 3:00 AM	14	2000
2018	Q2	TA	Amber	Start Passage	4/5/18 5:00 AM	25	0
2018	Q2	TA	Amber	a	6/22/18 12:00 AM	34	0
2018	Q2	TA	Amber	End passage	6/24/18 12:00 AM	31	0
2018	Q2	TA	Amber	Halt	6/26/18 12:00 AM	12	3000
2018	Q2	TB	Radian	Start Passage	6/26/18 3:00 AM	76	0
2018	Q2	TB	Radian	a	6/28/18 12:00 AM	85	0
2018	Q3	TB	Radian	b	7/14/18 12:00 AM	64
2018	Q3	TB	Radian	End passage	7/18/18 12:00 AM	54	0
2018	Q3	TB	Radian	Halt	7/22/18 12:00 AM	51	2000
2018	Q3	TB	Radian	Start Passage	7/22/18 6:00 AM	35	0
2018	Q4	TB	Radian	t	11/29/18 12:00 AM	45	0
2018	Q4	TB	Radian	End passage	12/24/18 12:00 AM	13	0
2018	Q4	TB	Radian	Halt	12/25/18 12:00 AM	12	50
2018	Q4	TB	Radian	Start Passage	12/25/18 12:00 AM	2	0
2019	Q1	TB	Radian	on road	1/1/19 12:00 AM	14	0
2019	Q1	TB	Radian	On road	1/3/19 12:00 AM	15	0

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

Anonymous

Not applicable

7 years ago

Hi all,

Year	Quarter	VesselCode	Bus Name	Stop Point	Date	Hrs	Load
2018	Q1	TA	Amber	Start Passage	3/15/18 12:00 AM	123	0
2018	Q1	TA	Amber	a	3/16/18 12:00 AM	12	0
2018	Q1	TA	Amber	End passage	3/20/18 12:00 AM	14	0
2018	Q1	TA	Amber	Halt	3/25/18 12:00 AM	32	2000
2018	Q1	TA	Amber	Start Passage	3/27/18 12:00 AM	23	0
2018	Q1	TA	Amber	e	3/28/18 12:00 AM	56	0
2018	Q2	TA	Amber	f	4/2/18 12:00 AM	67	0
2018	Q2	TA	Amber	g	4/3/18 12:00 AM	89	0
2018	Q2	TA	Amber	End passage	4/4/18 12:00 AM	1	0
2018	Q2	TA	Amber	Halt	4/5/18 3:00 AM	14	2000
2018	Q2	TA	Amber	Start Passage	4/5/18 5:00 AM	25	0
2018	Q2	TA	Amber	a	6/22/18 12:00 AM	34	0
2018	Q2	TA	Amber	End passage	6/24/18 12:00 AM	31	0
2018	Q2	TA	Amber	Halt	6/26/18 12:00 AM	12	3000
2018	Q2	TB	Radian	Start Passage	6/26/18 3:00 AM	76	0
2018	Q2	TB	Radian	a	6/28/18 12:00 AM	85	0
2018	Q3	TB	Radian	b	7/14/18 12:00 AM	64
2018	Q3	TB	Radian	End passage	7/18/18 12:00 AM	54	0
2018	Q3	TB	Radian	Halt	7/22/18 12:00 AM	51	2000
2018	Q3	TB	Radian	Start Passage	7/22/18 6:00 AM	35	0
2018	Q4	TB	Radian	t	11/29/18 12:00 AM	45	0
2018	Q4	TB	Radian	End passage	12/24/18 12:00 AM	13	0
2018	Q4	TB	Radian	Halt	12/25/18 12:00 AM	12	50
2018	Q4	TB	Radian	Start Passage	12/25/18 12:00 AM	2	0
2019	Q1	TB	Radian	on road	1/1/19 12:00 AM	14	0
2019	Q1	TB	Radian	On road	1/3/19 12:00 AM	15	0

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

Greg_Deckler
Community Champion
7 years ago
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
- Anonymous
  Not applicable
  7 years ago
  Greg_Deckler- You are awesome , thanks so much for the help

v-cherch-msft
Microsoft Employee
7 years ago
Hi Anonymous

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

Regards,

Cherie
Sum Dax help .pbix74 KB
- Anonymous
  Not applicable
  7 years ago
  Thanks so much for the help u guys are super cool , this community is very very helpful:)
- Anonymous
  Not applicable
  7 years ago
  v-cherch-msft - Thanks so much for the help u guys are super cool , this community is very very helpful

Forum Discussion

Sum Dax help

7 Replies

Related content

DAX: sum with filters

DAX: sum with two filters

DAX SUM with GROUP BY?

Dax - Sum values by date

IF SUM dax issue