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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors