Calculated Item overriding selected measure calculation

[LoooY]

Hi there,

I am a bit new to Power BI and ran into a problem.

First of all, I have data looks like this

Year	Month	Date	CentreCode	Item	Actual	Budget
2024	Jan	1/1/2024	1	Revenue	100	120
2024	Jan	1/1/2024	1	Cost	50	65
2024	Jan	1/1/2024	1	Net Profit	50	55
2024	Jan	1/1/2024	2	Revenue	200	240
2024	Jan	1/1/2024	2	Cost	100	150
2024	Jan	1/1/2024	2	Net Profit	100	90
2024	Feb	2/1/2024	1	Revenue	120	140
2024	Feb	2/1/2024	1	Cost	60	60
2024	Feb	2/1/2024	1	Net Profit	60	80
2024	Feb	2/1/2024	2	Revenue	240	260
2024	Feb	2/1/2024	2	Cost	120	120
2024	Feb	2/1/2024	2	Net Profit	120	140

Then I created following calculated items

Revenue = Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Revenue”} )

Cost = Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Cost”} )

Net Profit = Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Net Profit”} )

Net Profit % = Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Net Profit”} ) / Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Revenue”} )

And then I have following measure

Total Actual = sum (‘Data’ [Actual])

Total Budget = sum (‘Data’ [Budget])

Variance = Total Actual – Total Budget

I am expecting to get a table like this

	January
	Actual	Budget	Variance
Revenue	300	360	-60
Cost	150	215	-65
Net Profit	150	145	5
Net Profit %	50%	40.3%	9.7%

Instead, I am getting following result

	January
	Actual	Budget	Variance
Revenue	300	360	-60
Cost	150	215	-65
Net Profit	150	145	5
Net Profit %	50%	40.3%	8.3%

8.3% of net profit % is derived from calculated item formula, which is.

Net Profit % = Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Net Profit”} ) / Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Revenue”} )

But I am expecting the result is simply 50% - 40.3% (follow the selected measure : Variance = Total Actual – Total Budget)

Any advice?

One more question, how shall I do conditional format for Variance column? For variance in cost, + - sign is reversed.

	January	Format
	Actual	Budget	Variance	I Want
Revenue	300	360	-60	-60
Cost	150	215	-65	65
Net Profit	150	145	5	5

Thank you very much.

[LoooY · Accepted solution]

Thank you. I solved the problem.

First of all, I created a sperate measure like XU suggested~

And then, I modified my calculated item formula 

[v-zhengdxu-msft]

Hi @LoooY 

Please try this:

I create a new measure which can get right outcome:

Measure = 
VAR _currentMonth = SELECTEDVALUE(Data[Month])
	RETURN
		IF(
			SELECTEDVALUE(Data[Item]) = "Net Profit",
			SUMX(
				FILTER(
					'Data',
					'Data'[Item] = "Net Profit"
				),
				'Data'[Actual]
			) / SUMX(
				FILTER(
					ALLSELECTED('Data'),
					'Data'[Month] = _currentMonth && 'Data'[Item] = "Revenue"
				),
				'Data'[Actual]
			) - SUMX(
				FILTER(
					'Data',
					'Data'[Item] = "Net Profit"
				),
				'Data'[Budget]
			) / SUMX(
				FILTER(
					ALLSELECTED('Data'),
					'Data'[Month] = _currentMonth && 'Data'[Item] = "Revenue"
				),
				'Data'[Budget]
			)
		)

Then change the Variance:

Variance =
IF (
    SELECTEDVALUE ( Data[Item] ) <> "Cost",
    [Total Actual] - [Total Budget],
    ABS ( [Total Actual] - [Total Budget] )
)

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LoooY]

Thank you Xu

I have a calculated item for net profit % which is

Net Profit % = Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Net Profit”} ) / Calculate ( SELECTEDMEASURE(), Data’[Item] in {“Revenue”} )

Is there any way we can start from here? As I want to display net profit % for Actual and Budget as well.