Formulas in a Measure

[Reservoirdawgs]

I am trying to make a measure that combines a starting budget and a revised budget to create a final budget. As an example of what I am looking for, some line items have a starting budget (say $200) and no revised budget ($0), so the final budget would be $200. Some line items had a starting budget of $500 and a revised budget of $700, meaning that $200 was added to that line by the end of the fiscal year. Here's an example of what my dataset looks like:

Starting Budget Revised Budget (what I want) Final Budget
$500 $200 $700
$200 $0 $200
$1,000 $500 $1,500

I can't do a measure where I take the starting budget + (revised-starting) because the fields that have a $0 in the revised budget would then have a negative. What formula should I use to make this measure in PowerBI?

 

[Matt Allington]

Yes you can use a measure. https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

if you need a more precise response, you will need to post a link to a sample workbook containing realistic data

 

[Reservoirdawgs]

Yes you can use a measure. https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

if you need a more precise response, you will need to post a link to a sample workbook containing realistic data

Thank you for your help! I am embarrassed to say that I am not sure how to post a link to my sample workbook...is it okay if I link you to a picture of my table?

https://imgur.com/yEdSawd

The two columns I am interested in right now is the DS_Adopted Budget column (starting budget) and DS_Revised Budget column (ending budget). Basically, the starting budget is the money that was approved at the beginning of the year for each line item. As the year progresses, some changes to the line item might occur. When those changes occur, the Adopted Budget is changed to a Revised Budget. If you look on Row 25, you can see an example where the Adopted Budget (87,600) was revised to a new final budget (90,374). What that means is that during the year, $2,774 was added to that line item. If no changes were made during the year, then the revised budget just shows up as zero. So in row 1, you can see the Adopted Budget of 1,862,021 and a zero in the Revised Budget. What that means is that the line was not revised during the year, so the Adopted Budget always was the final budget.

I made a measure in my PowerBI report to try to get at the Final Budget. The formula is Final Budget = sumx(DS_Budget,if([Revised Budget]=0,[Adopted Budget],[Revised Budget])). That formula appears to work unless there is a zero in the Adopted Budget column and then there is a Revised Budget (nothing was budgeted at the beginning of the year but then during the course of the year money was budgeted in that line).

Any help would be appreciated. If more information is needed then anyone can PM me and I can email you my dataset.

 

[Matt Allington]

You can post links to files stored on OneDrive or Dropbox - both work. Sorry, I can't help with a picture.