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
apbiuser
Helper I
Helper I

Matching summarized/unsummarized tables

I believe I need a summarized table.

I am using a star schema model that is very clean.  I have a Budget table, Customer Table, Invoice Header Table, and Summarized Invoice Detail Table.

The Budget table has Company, Customer, Year, Period, Gross Sales budget dollars, and Gross Margin budget dollars.  There is a key field which is Year/Period/Company/Customer concatenated together.

The Customer table has customer and sales rep.

The Invoice Header table has Invoice Number, Year, Period, Company, Customer, SalesRep and a matching budget key field to the budget table.  It also has a key to the Customer table.

The Summarized Invoice Detail table has by Invoice number, Gross Sales, Gross Margin and a key matching to the Invoice Header table.  

The challenge is that for any given budget record, there may be multiple Invoice Header records each having a summarized invoice detal record. 

My users have asked for a table report that will show by Sales Rep how well Gross Sales(Sales/Budget Sales) and Gross Margin(Margin/Budgeted Margin) are doing compare to budget for a given Year/Period.  Based of that calculation I am to do conditional formatting on the cell.  I know how to do the conditional formatting.  I know how to use slicers.  I believe the answer involves using Summarized and Related functions but I have not found a good example.

Any suggestions?

1 ACCEPTED SOLUTION

Thanks Tom, that was a good start.

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey,

please have a look at this site:
https://www.daxpatterns.com/patterns/

There is pattern called "Budget Paatern" and "Handling different granularities"

As far as I understand your requirements, this should get you started.

Regards
Tom


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks Tom, that was a good start.

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.