Allocating facts in a many to many model

In this article I'll demonstrate a tricky modelling problem related to M2M, and how we can use DAX to overcome these problems. Specifically, duplication from the 1-side.

Let me explain the model. We have Sales, Partners, and a bridge table for SalesPartners. This is to enable the many to many relationship between Sales and Partners. I appreciate this is a contrived example but the M2M many-1 issue is very common.

When working with Power BI with this model we can drag values from a many table that cause the 1 side to duplicate. Even with this duplication, we see the sub-totals are correct. Problems come when users export to Excel. Power BI doesn't currently provide the sub-totals on Export.

This can cause user confusion as when they try and sum in Excel they calculate incorrect values.

Users have asked me multiple times with a M2M model to allocate fact values based on bridge table values. We also see this pattern in Budget Allocations.

I'll take you through some options to handle this, allowing users to use Excel without fear of double counting.

If only Power BI exported totals this would avoid having to write DAX.

The scenario we have to deal with is the users wants to divide the Sales Amount by the number of partners contributing to the Sales Order.

There are numerous ways to do this. This is just one simple method.

1. Create a calculated column in SalesPartners to count the number of partners working on each Sale

2. Create measures to divide the Sales Amount by the maximum partner number for each Sale

3. Create a measure to fix the sub-total, ensuring sales match the value in the Sales table.

1. PartnersPerOrder = CALCULATE(COUNTROWS(PartnersSales),ALLEXCEPT(PartnersSales,PartnersSales[SalesOrderID]))

2. Max Partners per Order = MAX(PartnersSales[PartnersPerOrder])

3. SalesAmountPerPartner = IF(HASONEVALUE(Sales[SalesOrder]),[TotalSales] /  [Max Partners per Order], sum(Sales[SalesAmount]))

Another option is to create this in a measure. By iterating over Sales Order ID's in the Partner table we can avoid the MAX().

MeasureSalesAmountPerPartner =
IF (
    HASONEVALUE ( PartnersSales[SalesOrderID] ),
    SUM ( Sales[SalesAmount] )
        / SUMX (
            VALUES ( PartnersSales[SalesOrderID] ),
            CALCULATE (
                COUNTROWS ( PartnersSales ),
                ALLEXCEPT (
                    PartnersSales,
                    PartnersSales[SalesOrderID]
                )
            )
        ),
    SUM ( Sales[SalesAmount] )
)

You can see how this enables us to remove the duplication in Sales and allow the Table to be exported.

You'll find the model, measures, report in the PBIX file on dropbox

Of course the Analyse in Excel functionality does provide distinct sub-totals, hence, that can also be an option if users are skilled in using Pivot Tables.

You do need to be aware that this approach can be expensive when the bridge and fact tables are very large.

I am interested to hear from members of the Power BI community's thoughts on this problem. M2M require advanced modelling skills in my opinion. This post is designed to show a typical problem that can't be managed with a simple star schema we see so often from training material.

I must thank Alberto and Marco as their book - the definitive guide to DAX has helped me in a big way.