Combining 2 fact tables with multiple dimensions?? Or dax formlula to show variance between 2 tables

[Anonymous]

Hi team, Im needing some help with how to tackle this.... I have 2 fact tables, first is my invoiced revenue (from billing system) which has a number of dimensions that we need to analyze the revenue in detail, the second fact table is our General ledger revenue. they share some common dimensions like account code, activity center, date. Often the $ value will reconcile at and account, acitivity centre, date level, but often manual adjustments are done outside of our billing system and we will have variances. We also have some activity centers where the revenue doesnt come from our billing 

We want to be able to analyse our revenue using the dimensions in the revenue fact table, but we need a 'reconciling' amount in there for the variances so we  always see the analysis as per our general ledger. 

Maybe its combining the fact tables or do I just need a dax formula?

Any help/ suggestions very much appreciated!

[edhans]

IMHO, you should merge these tables in PowerQuery and do as much of your analysis there, then bring in what you need with the appropriate amount columns, variances, etc., all at the lowest level of necessary granularity.

That will make it easy in DAX to create measures for summaries or other analysis, as well as making charting easier.