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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.