1 Report 2 Tables (3 Repair types - maybe) and Summaries for budgets

[WAB_Reports]

Hello all,

First let me thank the community for the help so far in my new journey (after being tossed into this) in Power Bi, DAX and everything else that falls under it. I have a report I'm trying to build with a trend of budgets vs cost for multiple sites and vehicle types (and maybe repair types). Here are the two sample tables I'm basing this on (both are SQL sourced, same DB different tables and are actually much larger with other data points, such as work order IDs for table 2 etc):

Table 1: site_veh_budget

Site	Vehicle Type	Date (D/M/Y)	Budget
A	Car	1/1/2020	10000
A	Truck	1/1/2020	20000
A	Motorcycle	1/1/2020	5000
B	Car	1/1/2020	12000
B	Truck	1/1/2020	15000
C	Motorcycle	1/1/2020	5500
C	Car	1/1/2020	8000
…	…	…	…
A	Car	1/11/2020	8888
A	Truck	1/11/2020	11000
A	Motorcycle	1/11/2020	5200
B	Car	1/11/2020	12000
B	Truck	1/11/2020	15000
C	Motorcycle	1/11/2020	5300
C	Car	1/11/2020	9000

Table 2: site_veh_repair (this is based on work or purchase orders/tickets)

Site	Vehicle Type	Date (D/M/Y)	Repair Type	Cost
A	Truck	6/1/2020	OOW	333.45
C	Car	3/11/2020	W	580.2
A	Truck	12/1/2020	M	75.5
C	Car	20/11/2020	M	450
B	Car	11/8/2020	M	92.6
B	Truck	12/1/2020	OOW	56.56
A	Car	11/11/2020	W	890.02
C	Motorcycle	20/1/2020	W	785.36
B	Truck	7/11/2020	W	2100.56
A	Motorcycle	14/1/2020	M	233.51
C	Motorcycle	7/11/2020	M	200.2
A	Motorcycle	22/3/2020	OOW	86.66
A	Truck	22/3/2020	W	1110.36
…	..	..	..	..

What I'm looking to do (still haven't picked a chart type) is take the monthly sums of each site+vehicle type per month (Table 2) and then site+vehicle type+total budget per month (Table 1) with a result of (Table or measures??) ==> (site+vehicle type+total budget per month) - (Site+totals per vehicle type per month).

Example:

Site	Vehicle Type	Date (D/M/Y)	Budget	Site Total	Difference
A	Car	1/11/2020	8888	9547	-659
A	Truck	1/11/2020	11000	8654	2346
A	Motorcycle	1/11/2020	5200	4589	611
B	Car	1/11/2020	12000	14823	-2823
B	Truck	1/11/2020	15000	18367	-3367
C	Motorcycle	1/11/2020	5300	4823	477
C	Car	1/11/2020	9000	11852	-2852

Bonus?! Should I build table 3 if I later want to expand the reportability?

Site	Vehicle Type	Date (D/M/Y)	total Cost OOW	total Cost W	total Cost M	type totals
A	Car	1/11/2020	6200	458	2889	9547
A	Truck	1/11/2020	5100	1221	2333	8654
A	Motorcycle	1/11/2020	1547	2412	630	4589
B	Car	1/11/2020	8100	4674	2049	14823
B	Truck	1/11/2020	5600	5296	7471	18367
C	Motorcycle	1/11/2020	3200	587	1036	4823
C	Car	1/11/2020	10254	598	1000	11852

My relationship is set to be many to many for Site in both tables. I have the date using the first of the month as right now I'm only needing data per month.

[v-easonf-msft]

Hi, @WAB_Reports 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.
It will help other community members easily find the solution when they get the similar issue.

Best Regards,
Community Support Team _ Eason

[sevenhills]

I tried uploading the .pbix file but I did not find the option, so I have to do the screenshots.

(Previously, I was able to upload files; Now it is not allowing with the chrome/edge browser. Has to check why ... out of thread context, fyi)

[sevenhills · Accepted solution]

In my view, you should avoid many-to-many

Data model and relationships

1. Create master list of Site from both tables

2. Create master list of Vehicle from both tables

3. Link master - Site, Vehicle, to both site_veh_budget, site_veh_repair. By doing steps 1 to 3, you are avoiding many-to-many.  

4. Create measure in site_veh_repair as "Diff Measure"

             Diff Measure 2 = Sumx(site_veh_budget, site_veh_budget[Budget]) - sumx(site_veh_repair, site_veh_repair[Cost])

Diff Measure = Sum(site_veh_budget[Budget]) - sum(site_veh_repair[Cost])

Adjust the formula for the dates, if you want like month and year to be considered

Coming to table visual, it will be easy drag ... 

=============================

Other way is to customize only for your requirement, https://community.powerbi.com/t5/Desktop/calculate-target-vs-total-sale/m-p/196598 

[WAB_Reports]

Thanks @sevenhills ! I'll work with this and see how it comes out!