topic Re: How to disaggregate a total amount into lower levels in DAX Commands and Tips

How to disaggregate a total amount into lower levels

Anonymous — Sun, 30 Jun 2019 00:18:53 GMT

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.

Re: How to disaggregate a total amount into lower levels

Zubair_Muhammad — Sun, 30 Jun 2019 05:26:48 GMT

Anonymous

Hi,

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

Re: How to disaggregate a total amount into lower levels

Anonymous — Sun, 30 Jun 2019 20:00:37 GMT

Beautiful. Thanks Zubair_Muhammad

Re: How to disaggregate a total amount into lower levels

Anonymous — Fri, 05 Jul 2019 17:51:40 GMT

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.

Re: How to disaggregate a total amount into lower levels

Anonymous — Sat, 06 Jul 2019 07:56:49 GMT

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

Re: How to disaggregate a total amount into lower levels

Anonymous — Thu, 11 Jul 2019 18:19:48 GMT

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?

Re: How to disaggregate a total amount into lower levels

Anonymous — Fri, 12 Jul 2019 08:16:34 GMT

Have a look at this:

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

Best

Darek