Totals across different tables with different granularity

[Anonymous]

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).

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.

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.

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!

Many thanks,

Paddy

[Anonymous]

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]

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 · Accepted solution]

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.