Find sum by grouping on different columns

[Anonymous]

Hello Community,

I have a Power BI report with different tabs. I have built a tab where the data is coming from 3 different tables, 2 of them from sql server (fact tables) and 1 from a custom made table using power query. Columns 'WeekNum' and 'Year' comes from the custom made table and is of text data type and whole number data type. Below is the sample table provided:

Date	WeekNum	Year	LocationNo	LocationName	City	Province	Market	Region	Division	Amount
11/29/2019	1	2019	1	abc	Waukegan	IL	Chicago	Central	RUTE	$123.00
11/29/2019	1	2019	412	def	Parsons	KS	Joplin-Pittsburg	Central	RUTP	$55.00
11/30/2019	1	2019	3902	ghi	Milton	WI	Milwaukee	Central	RUTE	$54.60
11/30/2019	1	2019	3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$89.00
11/30/2019	1	2019	4016	mno	Cary	IL	Chicago	Central	RUTE	$45.00
12/1/2019	1	2019	1	abc	Waukegan	IL	Chicago	Central	RUTE	$14.00
12/1/2019	1	2019	3902	ghi	Milton	WI	Milwaukee	Central	RUTE	$36.80
12/2/2019	1	2019	349	vwx	Columbiana	OH	Akron	Northeast	RUTE	$75.30
12/2/2019	1	2019	3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$981.00
12/2/2019	1	2019	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$33.00
12/3/2019	1	2019	1	abc	Waukegan	IL	Chicago	Central	RUTE	$99.55
12/3/2019	1	2019	412	def	Parsons	KS	Joplin-Pittsburg	Central	RUTP	$0.98
12/4/2019	1	2019	4016	mno	Cary	IL	Chicago	Central	RUTE	$90.02
12/4/2019	1	2019	3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$34.50
12/4/2019	1	2019	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$2.90
12/4/2019	1	2019	3902	ghi	Milton	WI	Milwaukee	Central	RUTE	$6.57
12/5/2019	1	2019	1	abc	Waukegan	IL	Chicago	Central	RUTE	$15.56
12/5/2019	1	2019	412	def	Parsons	KS	Joplin-Pittsburg	Central	RUTP	$58.87
12/6/2019	2	2019	3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$21.60
12/6/2019	2	2019	412	def	Parsons	KS	Joplin-Pittsburg	Central	RUTP	$13.60
12/7/2019	2	2019	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$11.35
12/7/2019	2	2019	1	abc	Waukegan	IL	Chicago	Central	RUTE	$312.00
12/7/2019	2	2019	3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$10.46
12/8/2019	2	2019	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$75.60
12/8/2019	2	2019	3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$16.56
11/27/2020	1	2020	4016	mno	Cary	IL	Chicago	Central	RUTE	$6.98
11/27/2020	1	2020	1	abc	Waukegan	IL	Chicago	Central	RUTE	$300.00
11/28/2020	1	2020	412	def	Parsons	KS	Joplin-Pittsburg	Central	RUTP	$20.65
11/28/2020	1	2020	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$6.98
11/28/2020	1	2020	3902	ghi	Milton	WI	Milwaukee	Central	RUTE	$258.00
11/29/2020	1	2020	1	abc	Waukegan	IL	Chicago	Central	RUTE	$69.00
11/29/2020	1	2020	349	vwx	Columbiana	OH	Akron	Northeast	RUTE	$6.74
11/30/2020	1	2020	4016	mno	Cary	IL	Chicago	Central	RUTE	$2.58
11/30/2020	1	2020	349	vwx	Columbiana	OH	Akron	Northeast	RUTE	$0.69
12/1/2020	1	2020	412	def	Parsons	KS	Joplin-Pittsburg	Central	RUTP	$29.47
12/1/2020	1	2020	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$47.69
12/1/2020	1	2020	3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$68.30
12/2/2020	1	2020	3902	ghi	Milton	WI	Milwaukee	Central	RUTE	$11.98
12/2/2020	1	2020	1	abc	Waukegan	IL	Chicago	Central	RUTE	$11.77
12/2/2020	1	2020	412	def	Parsons	KS	Joplin-Pittsburg	Central	RUTP	$9.60
12/3/2020	1	2020	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$29.54
12/3/2020	1	2020	4016	mno	Cary	IL	Chicago	Central	RUTE	$56.12
12/4/2020	2	2020	3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$40.96
12/4/2020	2	2020	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$9.23
12/4/2020	2	2020	3902	ghi	Milton	WI	Milwaukee	Central	RUTE	$143.95
12/4/2020	2	2020	349	vwx	Columbiana	OH	Akron	Northeast	RUTE	$0.18
12/4/2020	2	2020	487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$233.80

I want to find the sum of 'Amount' for each location per week. I'm skeptical about pivoting the table in data source as I have other tabs in the report which I don't want to mess around with. Is there a way to achieve it using a DAX query? Below is my expected result:

LocationNo	LocationName	City	Province	Market	Region	Division	Week 1 - 2019	Week 1 - 2020	Week 2 - 2019	Week 2 - 2020
1	abc	Waukegan	IL	Chicago	Central	RUTE	$252.11	$380.77	$312.00	$0.00
349	vwx	Columbiana	OH	Akron	Northeast	RUTE	$75.30	$7.43	$0.00	$0.18
412	def	Parsons	KS	Joplin-Pittsburg	Central	RUTP	$114.85	$59.72	$13.60	$0.00
487	fgh	Clayton	GA	Atlanta	Southeast	RUTE	$35.90	$84.21	$86.95	$243.03
3902	ghi	Milton	WI	Milwaukee	Central	RUTE	$97.97	$269.98	$0.00	$143.95
3961	jkl	Gunnison	UT	Salt Lake City	West	NRUTTech	$1,104.50	$68.30	$48.62	$40.96
4016	mno	Cary	IL	Chicago	Central	RUTE	$135.02	$65.68	$0.00	$0.00

I have tried creating measures for each week but I'm not getting the correct result. I'm providing one such measure:

Week1 = Var mon = 1

return

CALCULATE(sum(Query1[Amount]), CONVERT(Custom_Date[WeekNum],INTEGER)=mon, FILTER(Custom_Date,Custom_Date[Year]=[Year]))+0

[mahoneypat]

Just use this measure in the values area of the matrix

NewMeasure = SUM(Query1[Amt]) + 0

[Anonymous]

@mahoneypat Thank you so much for the solution! Could you please elaborate a little on how to show 0's? For example, in the above sample dataset, location number 349 has no amount in week 2 of 2019. What will be the new measure and on which column? I'm not quite understanding it well. Kindly help me!

[mahoneypat · Accepted solution]

What you are looking for can be obtained just with a matrix visual with Year and Week on columns.  See pic below.  No measure was needed.  However, if you want to show 0s, you can make a simple SUM measure on that column and just add "+ 0".

Also, FYI that there is a simpler way to to make your Date tables vs. making 2019 and 2019 separately, and then appending.  If you do use that approach, FYI too that you can right click in the query editor and uncheck Enable Load so that tables not used in visuals/analysis are not loaded.  File attached too.

Regards,

Pat

[Anonymous]

@mahoneypat I'm providing a link to the https://drive.google.com/file/d/1B7fQBz_3XS4pL18cxilinPIXsxAIKQfJ/view?usp=sharing  sample file for your reference. The expected output is mentioned in my post description. Also, please note that I have applied filters in the 'ReportedTotalDate' column as I'm looking to evaluate only the last week of November and December. Also, I had to create 'Custom_Date' table in order to add a custom week number (WeekNum) column. 'Query1' and 'Query2' are fact tables in my sample dataset as well as in this sample dataset. Now, in order to find sum of amount per week for each location and in respective years, I had thought of creating 4 new measures, for example, if I want to create a measure for Week1-2019, it would be as following: Week1_2019 = var vThisValue = SELECTEDVALUE(WeekTable[Value]) return CALCULATE(SUM(Query1[Amount]), CONVERT(Custom_Date[WeekNum],INTEGER) = vThisValue,FILTER(Query1,Query1[FundraisingYear]=[Year]))+0 , but this is not returning any value. 

[mahoneypat]

@Anonymous  If you can reply with a link with your pbix (or a mock up of it), I can get it working.  I wasn't thinking you'd still do the CONVERT part.  Thanks for the suggestion @PaulDBrown 

Regards,

Pat

[Anonymous]

@mahoneypat I'm not getting any value. It's just returning blank values. I'll explain you the steps that I have gone through so that you can guide me if I'm doing anything wrong at any step:

1. Created a cutom table and named it WeekTable using the formula GENERATESERIES(1,5)

2. Created a relationship between WeekTable and the other table named 'Custom_Date' which has week number (This table is also a custom made table). 

3. Since, I need to find the total amount per week for each location and separately for 2019 and 2020, I first tried creating a measure to find the sum of amount for 2020. For this, I have used the formula:

Weekly Sum = var vThisValue = SELECTEDVALUE(WeekTable[Value]) return CALCULATE(SUM(Query1[Amount]), CONVERT(Custom_Date[WeekNum],INTEGER) = vThisValue,FILTER(Query1,Query1[FundraisingYear]=[Year]))+0 

(Since, the week number is of 'text' data type, I had to use the convert function to change it into Integer).

I believe there's something wrong with CONVERT(Custom_Date[WeekNum],INTEGER).  If I don't use 'CONVERT', it gives me an error to convert text data type to integer.

[mahoneypat]

I wondered if that would happen, but was in a hurry.  Please try same but pull that into a variable first.

Weekly Sum = var vThisValue = SELECTEDVALUE(WeekTable[Value])
Return CALCULATE(SUM(Query1[Amt]), Query1[WeekNum] = vThisValue)

Regards,

Pat

[Anonymous]

@mahoneypat It is throwing an error "A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed".  As mentioned, column 'WeekNum' is not coming from Query1 (fact table) but from a custom made table 'Custom_date'. Also, how will I get the total sum of each location no rolled up to each week for 2019 and 2020? Kindly help me as I'm stuck in this issue. Any help is highly appreciated!

[mahoneypat]

You could add a disconnected table with GENERATESERIES(1,10) for example, use that column on columns in your matrix and then use the SELECTEDVALUE() of that new table column in your formula.  That way you need just one measure.

Weekly Sum = CALCULATE(SUM(Query1[Amt]), Query1[WeekNum] = SELECTEDVALUE(WeekTable[Value]))

Regards,

Pat