Forum Discussion
Calculated Item overriding selected measure calculation
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.
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
3 Replies
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
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.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.
