How to disaggregate a total amount into lower levels

[Anonymous]

Hey all,

Having trouble figuring out the DAX here. Want to create a calculated column that will disaggregate the paid to date for a lawsuit by the separate invoices. For Example: $1000 has been paid out for Lawsuit Key Value 1. There are 4 invoices for that lawsuit, thus in column 5 the value needs to be 250 for Lawsuit Value 1. What is the DAX formula here? Thanks all! Pic below.

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Hi,

Goal =
[Amount paid to date for law suit]
    / CALCULATE (
        COUNTROWS ( TableName ),
        ALLEXCEPT ( TableName, TableName[Lawsuitkey] )
    )

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Hi,

Goal =
[Amount paid to date for law suit]
    / CALCULATE (
        COUNTROWS ( TableName ),
        ALLEXCEPT ( TableName, TableName[Lawsuitkey] )
    )

[Anonymous]

Hey @Zubair_Muhammad  One more question. What then would be the DAX formula to calculate the percent change between the amount paid and the amount budgeted. The formula is in the picture I've attached. You've been a tremendous help!

Best.

[Anonymous · Accepted solution]

Here's the formula for your column:

Percent Change = 
var __actual = TableName[Actual Paid to Date Spread]
var __budgeted = TableName[Budgeted for Lawsuit]
var __percentChangeFromBudgeted =
    DIVIDE(
        __actual - __budgeted,
        __budgeted
    )
return
    __percentChangeFromBudgeted

Please always precede the name of a column with the name of the table it belongs to and never precede the name of a measure with the name of the table it belongs to.

Second, the formula does not multiply by 100 because this is not how it should work. It returns a number and you have to format it as percentage using the formatting group in the ribbon:

Hope this helps.

Best

Darek

[Anonymous]

@darlove

How can I calculate then instead of just disaggregating the budget amount evenly across invoices, how can I calculate the percent the invoice is to the total net amount for invoices charged to that specific lawsuit and then divide the budget based upon that percent?

[Anonymous]

Have a look at this:

https://www.daxpatterns.com/budget-patterns/

Best

Darek

[Anonymous]

Beautiful. Thanks @Zubair_Muhammad