Forum Discussion

Frank's avatar
Frank
Frequent Visitor
5 years ago

Allocating Fact Table to Multiple Dimension Tables

I've searched high and low for this question but it seems most results are about multiple fact tables.   What I have: I have a basic star schema that comes from SQL Server.  A single fact table of ...
Need Help
Frank's avatar
Frank
Frequent Visitor
5 years ago

Hmmm...so I accidentally stumbled into a working solution.

 

I made a measure Sum of Share on the allocation table.  It is a many-to-many relationship so RELATED would not work, and I struggled to get the alternatives I found on here to work for my case.  I don't know why I couldn't figure out how to do this inside the fact table measure, and just gave this a shot when all else failed.

 

I changed my fact table measure Sum of Revenue = SUMX(FactTable, FactTable[Revenue] * [Sum of Share]) and let the relationships do most of the work.  I assume I can just keep multuplying new allocated dimensions.

 

This doesn't seem so bad just working with sums. I did have to add a little bit on the SQL side so the allocation table plugs missing and out of balance customers.

 

Am I painting myself into a corner?  Or will this hold up?