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.
Hi,
I'm a DAX/BI beginner and I'm struggling with a formula. I currently have measures that sum up my financial data and I then created a simple Difference using Actual - Budget. This works fine for Income transactions (it shows as positive and negative figures correctly), but for Expense transaction I would want it to show Budget - Actual.
I was wondering if this could be achieved by basing the calculation on criteria in another column, something like;
Difference =
if ('Main'[I&E] = "Income", 'Main'[Actuals] - 'MainBudget'[Budget],
if ('Main'[I&E] = "Expense",'MainBudget'[Budget] - 'Main'[Actuals] )
However this tells me that a single value for column 'I&E' cannot be determined.
Could anybody advise how to achieve the effect I'm looking for?
Many thanks
Can you provide some sample data?
Hi,
I've simplified the data for ease;
Table: Main
Tran No | Date | Type | Details | Total | I&E |
52776 | 01/02/2017 | Fundraising | Tea Event | 400.00 | Income |
53754 | 01/02/2017 | Donations | Community donations | 100.00 | Income |
52834 | 01/02/2017 | Donations | Community donations | 30.00 | Income |
53015 | 01/02/2017 | Events | Deposit returned | 100.00 | Expense |
52833 | 01/02/2017 | Events | Tea Event | 98.00 | Income |
52845 | 01/02/2017 | Retail | Goods sold | 514.00 | Income |
52900 | 01/02/2017 | Events | Raffle | 50.00 | Income |
72367 | 01/02/2017 | Events | Fundraising costs | 500.00 | Expense |
53016 | 01/02/2017 | Stationery | Main office | 10.00 | Expense |
72365 | 01/02/2017 | Travel | Fundraising | 23.50 | Expense |
53146 | 01/02/2017 | Donations | Refund | 10.00 | Income |
I then have a Measure to sum it; Actuals = SUM(Main[Total])
I have a separate Budget table with a similar measure to get the Budget totals. The Budget and Main table are linked by a Date table.
I then have a difference calculated by; Difference = 'Main'[Actuals] - 'Budget'[Budget].
I created a Matrix visualisation showing Budget, Actual and Difference
Income | Actuals | Budget | Difference | |
Donations | 130 | 100 | 30 | |
Events | 148 | 150 | -2 | |
Fundraising | 400 | 500 | -100 | |
Retail | 514 | 350 | 164 | |
Expense | ||||
Events | 400 | 200 | 200 | |
Stationery | 10 | 40 | -30 | |
Travel | 23.5 | 30 | -6.5 |
But what I would like to see is;
Income | Actual | Budget | Difference | |
Donations | 130 | 100 | 30 | |
Events | 148 | 150 | -2 | |
Fundraising | 400 | 500 | -100 | |
Retail | 514 | 350 | 164 | |
Expenses | ||||
Events | 400 | 200 | -200 | |
Stationery | 10 | 40 | 30 | |
Travel | 23.5 | 30 | 6.5 |
Essentially I want the signs flipped round on Difference when it is an Expense.
I'm unable to share my actual data or model, so hope this has made it clearer!
Thank you
Can you change your difference to:
Difference = IF('Main'[I&E]="Income",'Main'[Actuals] - 'Budget'[Budget],'Budget'[Budget] - 'Main'[Actuals])
?
Hi,
This brings up the message
"A single value for column 'I&E' in table 'Main' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Hi @KSt,
Did you use a measure to calculate the difference? If so, please change to create a calculated column using the same formula for a test.
Difference =
if ('Main'[I&E] = "Income", 'Main'[Actuals] - 'MainBudget'[Budget],
if ('Main'[I&E] = "Expense",'MainBudget'[Budget] - 'Main'[Actuals] )
If issue persists, please share the sample data of Budget table so that I can reproduce your scenario.
Best regards,
Yuliana Gu
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 |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |