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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Totals across different tables with different granularity

Hi,

 

I have two fact tables, one for Actuals and one for Forecast. I also have two lookup tables, for Account Code and one for Calendar. I've linked both fact tables to both lookup tables. (KSB1 as seen below is just the name for the Actuals table and GL is the name for account code).

paddygaul95_0-1632308539306.png

Both fact tables have the field 'Account Code', so with a simple SUM measure, both work as expected on a table together grouped by Account Code.

paddygaul95_1-1632308755208.png

 

However, the Actuals table has greater granularity than the Forecast Table, as it has data by purchase order, while the Forecast has values by account code. I'd like to use the PO description from the Actuals table on the same visual so the user can drill into the PO detail if needed. However, as the Purchase Orders on the Actuals Table don't have a relationship to the Forecast Table, when I expand the Account Code to include the PO Description taken from the Actuals Table, the Forecast measure simply repeats the same value for every PO, as seen below.

 

paddygaul95_2-1632308900643.png

 

I am hoping for a measure which will only calculate at the subtotal level for the Forecast, i.e. only calculate at the granularity contained within the Forecast Table. I've manually manipulated the table in Excel to show what this would look like, as shown below.

If anyone has any ideas for how this can be achieved I would be very grateful!

paddygaul95_3-1632309109366.png

 

Many thanks,

 

Paddy

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

That's rather easy. Create a hidden bridge table that associates PO's with their accounts and take it from there using the techniques outlined in the articles/videos.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

This is a typical granularity issue scenario which has been beaten to death in many places on the net. Here are some very useful materials that you could get familiar with in order to understand how to deal with such things:

 

Budget – DAX Patterns

Working below a DAX formula’s granularity - SQLBI

Summing values for the total - SQLBI

Anonymous
Not applicable

Hi @Anonymous 

thanks for getting back to me, I appreciate it. I have tried using those articles and videos on granularity but I can't find how to do it when the filter context in the rows is provided by the fact table, i.e. the purchase order description from the Actuals table as in this scenario. I'll keep trying!

Thanks, 

Paddy

 

Anonymous
Not applicable

That's rather easy. Create a hidden bridge table that associates PO's with their accounts and take it from there using the techniques outlined in the articles/videos.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors