Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@Anonymous
Hi,
Goal = [Amount paid to date for law suit] / CALCULATE ( COUNTROWS ( TableName ), ALLEXCEPT ( TableName, TableName[Lawsuitkey] ) )
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
Hi,
Goal = [Amount paid to date for law suit] / CALCULATE ( COUNTROWS ( TableName ), ALLEXCEPT ( TableName, TableName[Lawsuitkey] ) )
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.
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |