cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Pbi07
Helper IV
Helper IV

Totals from Second fact

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 

 

CustomerOrderOrder DateShip QuantityBacklog QuanityProd Type
1007000004/1/20201500nullPC
1007000085/1/20203000nullPC
1008000005/2/20201100nullDR
1018000016/10/2020700nullPC
1008000026/25/2020900nullDR
1018000036/28/20201200nullPC
1028000047/10/20201800nullPC
1019000014/1/20211000nullPC
1009000024/10/20211500nullDR
1019000034/26/20211000nullPC
1009000044/27/20211200nullPC
1009000055/1/20212000nullDR
1009000065/2/20213000nullPC
1009000075/3/2021null5000DR
1009000085/4/202110050PC
1009000095/8/2021null3000PC
1009000105/20/2021null1000DR
1009000116/25/2021null2500PC

 

Budget details 

PeriodCustomerProd TypeAmount
202005100PC100
202104100PC200
202105100PC1000
202105100DR2000
202105100MO2200

 

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. 

 

CustomerMTD SalesCumm Sales for YEARBudget
100$5,100$11,550$3,000

 

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

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User III
Super User III

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Pbi07
Helper IV
Helper IV

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.

CustomerProd TypeMTD SalesCumm Sales for YEARBudget
100DR200060002000
100HF  1350
100PC310055501000
101DR  2000
101HF  1350
101PC  1000
102DR  2000
102HF  1350
102PC  1000

 

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

Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture2.png

 

Budget =
CALCULATE (
SUM ( Budget[Amount] ),
TREATAS ( VALUES ( 'Calendar'[Month&Year Budget CC] ), Budget[Period] )
)
 
 
 
 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors