Totals from Second fact

[Pbi07]

Need help with modeling this Sales and the budget information. 

I have 2 fact tables , 1 with the Sales details and the other with the budget provisioned by month. 

Sales Details 

Customer	Order	Order Date	Ship Quantity	Backlog Quanity	Prod Type
100	700000	4/1/2020	1500	null	PC
100	700008	5/1/2020	3000	null	PC
100	800000	5/2/2020	1100	null	DR
101	800001	6/10/2020	700	null	PC
100	800002	6/25/2020	900	null	DR
101	800003	6/28/2020	1200	null	PC
102	800004	7/10/2020	1800	null	PC
101	900001	4/1/2021	1000	null	PC
100	900002	4/10/2021	1500	null	DR
101	900003	4/26/2021	1000	null	PC
100	900004	4/27/2021	1200	null	PC
100	900005	5/1/2021	2000	null	DR
100	900006	5/2/2021	3000	null	PC
100	900007	5/3/2021	null	5000	DR
100	900008	5/4/2021	100	50	PC
100	900009	5/8/2021	null	3000	PC
100	900010	5/20/2021	null	1000	DR
100	900011	6/25/2021	null	2500	PC

Budget details 

Period	Customer	Prod Type	Amount
202005	100	PC	100
202104	100	PC	200
202105	100	PC	1000
202105	100	DR	2000
202105	100	MO	2200

The report has a date slicer and the expected result is below if selected date is 6th May 2021. How to model this to get the budget amount only for the Product types sold in that month. 

Tried to link the two by concatenating the common fields, but still the budget totals messed up. 

Customer	MTD Sales	Cumm Sales for YEAR	Budget
100	$5,100	$11,550	$3,000

Pbix file is - https://drive.google.com/file/d/1neGxPlFQPQI3O9BFSfwXvCStw5F4sREO/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Pbi07]

Thanks @Jihwan_Kim 

Thanks for providing those detailed steps.

I noticed that there is an issue with it. The budget amount is applied for all customers. Also budget amount should be only for the products sold in the month. 

See the pbix attached.

Customer	Prod Type	MTD Sales	Cumm Sales for YEAR	Budget
100	DR	2000	6000	2000
100	HF			1350
100	PC	3100	5550	1000
101	DR			2000
101	HF			1350
101	PC			1000
102	DR			2000
102	HF			1350
102	PC			1000

https://drive.google.com/file/d/1neGxPlFQPQI3O9BFSfwXvCStw5F4sREO/view?usp=sharing

[Jihwan_Kim]

Hi, @Pbi07 

I am not sure about how your actual data model looks like, but please try to follow the below steps.

- create dim tables: Customers Table / Products Table

- create a new column in the Calendar Table that can be connected to the Budget Table [Period]

- change the data type of [Customer] in the sales table to a number type ( your sample shows text type).

- try to create relationships between fact tables and dim tables.

And last step is to write the below measure.

Please check the pbix file's link down below.

Budget =

CALCULATE (

SUM ( Budget[Amount] ),

TREATAS ( VALUES ( 'Calendar'[Month&Year Budget CC] ), Budget[Period] )

)

https://www.dropbox.com/s/k05733bmvqfwblk/Orders.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM