Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VincenzoChean
Helper I
Helper I

Actual vs budget Gross Profit

Hi, 

 

I have these tables

1) Actuals ( shows the actuals for the month/year in branches in different GL codes)

2) Budget ( shows the budget  for the month/year in branches in different GL codes)  

3) GL codes with categories( categories are Income/Expenses/Operating expenses) for each GL codes. 

4) branch ( different branch codes/name)

 

what i want to achive is similar to the below matrix. But I want to add Gross Profit for actual & budget.

Gross profit = Income - Cost of sales. 

 

How can i do this please. 

 

VincenzoChean_0-1650412033144.png

 

1 ACCEPTED SOLUTION

Hi @VincenzoChean ,

Here's my solution: https://drive.google.com/file/d/1Q6fheFcoEiL8dON959zf1FwUBc8T0M7S/view?usp=sharing

I ended up not using calculation groups but instead use a disconnected table to assist with the calculation - disconnected as it doesn't have any relationship to the facts or dimension tables.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

6 REPLIES 6
danextian
Super User
Super User

This can probably be done using Calculation Groups in Tabular Editor. Can you please post a download link to your pbix?






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

please see if you can download the pbix

Hi @VincenzoChean ,

Here's my solution: https://drive.google.com/file/d/1Q6fheFcoEiL8dON959zf1FwUBc8T0M7S/view?usp=sharing

I ended up not using calculation groups but instead use a disconnected table to assist with the calculation - disconnected as it doesn't have any relationship to the facts or dimension tables.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

thanks alot. i thought of doing a Category would do what i want. 

 

but why dont we need to relationship with other the flat tables ?

If you add a relationship between the Category columns in the helper table and GL Codes table, Gross Profit will appear as blank/null so you'll end up with just the same values before adding the helper table - no GP there as Gross Profit row value doesn't exist in GL Codes.  Try creating the relationship and you'll see what I mean.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.