Sum Dax help

[Anonymous]

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

[v-cherch-msft · Accepted solution]

Hi @Anonymous

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

Regards,

Cherie

[Anonymous]

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

[Anonymous]

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 · Accepted solution]

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]

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

[v-cherch-msft · Accepted solution]

Hi @Anonymous

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

Regards,

Cherie

[Anonymous]

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

[Anonymous]

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