Sum based on criteria in other column

[KSt]

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

[Greg_Deckler]

Can you provide some sample data?

[KSt]

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

[Greg_Deckler]

Can you change your difference to:

Difference = IF('Main'[I&E]="Income",'Main'[Actuals] - 'Budget'[Budget],'Budget'[Budget] - 'Main'[Actuals])

?

[KSt]

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."

[v-yulgu-msft]

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