Weighted Totals when Allocating Costs

[MG86]

Hi,

(Simplified example:) I have a table (Financial Mutations) with a Amount that is either Revenue or Costs. Each mutation is on a Row in the Table. Revenue rows are all matched to a Customer. However Costs do not always correspond to a single Customer and have no Customer assigned on the row.

I created two measures. One is Revenue which is CALCULATE(SUM(Amount),Revenue or Cost = "Revenue") and the other is Cost which is same formula but then Cost. 

I want to allocate the unassigned costs to the Customers, based on Revenue.

For this I created a formula (CostAllocationBasedOnRevenue) that first divides the Revenue by the Total Revenue, and then multiplies it with the sum of costs for all rows where Customer is Blank:

CostAllocationBasedOnRevenue = DIVIDE([Revenue],CALCULATE([Revenue],ALL('Financial Mutations'[Customer]))) * CALCULATE([Cost], 'Financial Mutations'[Customer] = BLANK())

This basically works. But the problem is that with this formula, the Total for the Customer is not weighted for the Category, which it needs to be.

Shown in a Matrix:

The individual amounts for the categories are what I want, but they do not add up 'correctly' (I understand that Power BI technically just divides the customer total by the grand total and returns a correct result in that sense).

So for example, how do I get customer A total AllocatedCosts to show 31.22 (5.77 + 25.45) on the subtotal line in the Matrix, rather than 28.81?

I tried several things such as a SUMX on a summarization of Category and Customer but no luck to a point where I spent too many hours and losing sight of it all. Which leads me to ask here if anyone could help me along?

[MG86]

Here's the PBIX example file (link valid for a week):

https://we.tl/t-pmc1lH0syO

---

[daxer-almighty]

Your calculations are wrong. Have a good look at them above... Here's a link to a file with correct calculations: https://1drv.ms/u/s!ApyQEauTSLtOgY8r0SeAOUgilUglFA?e=Gwl4HB

Here's why they're wrong. Customer C has 2 categories with the same revenue but different allocated costs. This, of course, is wrong. The costs must be the same since the percentages of the total revenue for those two entries are the same.

[MG86]

Hi daxer-almighty,

Thank you. However the goal is not to allocate based on grand total only. If the category of the unallocated costs are known, they need to be distributed among the category. So the allocated costs I had on category level were correct. The totals on customer level need to be weighted accordingly. 

[MG86]

I think I got it. I used your example of adding a calculated column stating if the costs are unassigned (TRUE/FALSE) and then used this Measure:

AllocatedCosts2 =
CALCULATE (
    SUMX (
        'Financial Mutations',
        DIVIDE (
            CALCULATE (
                SUM ( 'Financial Mutations'[Amount] ),
                'Financial Mutations'[Revenue or Cost] = "Revenue"
            ),
            CALCULATE (
                SUM ( 'Financial Mutations'[Amount] ),
                ALLEXCEPT ( 'Financial Mutations', 'Financial Mutations'[Category] ),
                'Financial Mutations'[Revenue or Cost] = "Revenue"
            )
        )
            * CALCULATE (
                SUM ( 'Financial Mutations'[Amount] ),
                ALLEXCEPT ( 'Financial Mutations', 'Financial Mutations'[Category] ),
                'Financial Mutations'[Revenue or Cost] = "Cost",
                'Financial Mutations'[Unassigned]
            )
    )
)

This way, the measure iterates the revenues first and divides it by total revenue for the category. Then it multiplies it by the total unassigned costs for that category. Then it sums it all up.

Is this an appropriate solution?
Edit; Struggling with getting it to work in my more complicated real-life model. Is the theory correct?

[daxer-almighty · Accepted solution]

Here is the measure that does what you want:

Allocated Cost = 
SUMX(
    DISTINCT( 'Financial Mutations'[Category] ),
    var __unallocatedCostForCategory = 
        CALCULATE(
            SUM( 'Financial Mutations'[Amount] ),
            ALLEXCEPT( 
                'Financial Mutations',
                'Financial Mutations'[Category]
            ),
            'Financial Mutations'[Amount Unassigned]
        )
    var __totalRevenueForCategory =
        CALCULATE(
            [Revenue],
            ALLEXCEPT( 
                'Financial Mutations',
                'Financial Mutations'[Category]
            )
        )
    var __revenueForCategory = [Revenue]
    var __percentage = 
        DIVIDE( 
            __revenueForCategory,
            __totalRevenueForCategory
        )
    var __result =
        __percentage * __unallocatedCostForCategory
    return
        __result
)

I have also updated the file. Please use the same link as above to retrieve it.

Your solution should not be used. You should not write DAX this way as it's extremely inefficient. You should never iterate fact tables row by row. NEVER. There are too many reasons to state. If you want to know why it's a no-no, please read the book "The Definitive Guide to DAX" by Marco Russo and Alberto Ferrari. Iterated fact tables are in fact expanded tables and this not only puts a lot of stress on the calculation engine, but it also makes code prone to error. And on top of that, if there are multiple SAME rows in the table they are treated as one and the same row. This may introduce subtle errors you will never be able to spot.

[MG86]

Hi,

Thanks for all the help. It works like a charm on the provided sample data. Still struggling with actual data but this gets me further.

Edit: Ran into another issue which I had posted here but that was a simple mistake on my side.