Variance Analysis

[Anonymous]

I have below "values" table:

Account	Client	Service Line	Ops Geo	Year	Period	Scenario	Data Source	Currency	Measure	Manager	RVP	[Values]	COR	GDR Total	COR Value	Revenue	Revenue Value	SGA	SGA Value
1000	100	Finance	Oshkosh	2020	1	Budget	Input	USD	Value	ABS	DGS	400		Revenue	0	Revenue	400		0
1000	200	Accounting	Regina	2020	2	Budget	Input	USD	Value	ABS	DGS	400		Revenue	0	Revenue	400		0
1000	100	IT	Oshkosh	2020	3	Budget	Input	USD	Value	ABS	DGS	400		Revenue	0	Revenue	400		0
2000	200	Finance	Regina	2020	4	Budget	Input	USD	Value	ABS	DGS	200	COR	COR	200		0		0
2000	100	Accounting	Oshkosh	2020	5	Budget	Input	USD	Value	ABS	DGS	100	COR	COR	100		0		0
2000	200	IT	Regina	2020	6	Budget	Input	USD	Value	ABS	DGS	200	COR	COR	200		0		0
3000	100	Finance	Oshkosh	2020	7	Budget	Input	USD	Value	ABS	DGS	100		SGA	0		0	SGA	100
3000	200	Accounting	Regina	2020	8	Budget	Input	USD	Value	ABS	DGS	200		SGA	0		0	SGA	200
3000	100	IT	Oshkosh	2020	9	Budget	Input	USD	Value	ABS	DGS	100		SGA	0		0	SGA	100
1000	200	Marketing	Regina	2020	10	Budget	Input	USD	Value	ABS	DGS	200		Revenue	0	Revenue	200		0
1000	100	Finance	Oshkosh	2020	1	Actual	Input	USD	Value	ABS	DGS	500		Revenue	0	Revenue	500		0
1000	200	Accounting	Regina	2020	2	Actual	Input	USD	Value	ABS	DGS	500		Revenue	0	Revenue	500		0
1000	100	IT	Oshkosh	2020	3	Actual	Input	USD	Value	ABS	DGS	300		Revenue	0	Revenue	300		0
2000	200	Finance	Regina	2020	4	Actual	Input	USD	Value	ABS	DGS	200	COR	COR	200		0		0
2000	100	Accounting	Oshkosh	2020	5	Actual	Input	USD	Value	ABS	DGS	200	COR	COR	200		0		0
2000	200	IT	Regina	2020	6	Actual	Input	USD	Value	ABS	DGS	100	COR	COR	100		0		0
3000	100	Finance	Oshkosh	2020	7	Actual	Input	USD	Value	ABS	DGS	200		SGA	0		0	SGA	200
3000	200	Accounting	Regina	2020	8	Actual	Input	USD	Value	ABS	DGS	100		SGA	0		0	SGA	100
3000	100	IT	Oshkosh	2020	9	Actual	Input	USD	Value	ABS	DGS	200		SGA	0		0	SGA	200
1000	200	Marketing	Regina	2020	10	Actual	Input	USD	Value	ABS	DGS	100		Revenue	0	Revenue	100		0

I created the following measures:

Revenue = sum('values'[Revenue Value])

SGA = SUM('values'[SGA Value])

COR = sum('values'[COR Value])

Blank EBITDA = calculate([EBITDA], filter('values',[EBITDA]<>0))

Variance = calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Actual")) - calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Budget"))

I am trying to show Actual, Budget, and the Variance (Actual-Budget) in one matrix table visualization. 

I get the below tables when I use Blank Ebitda and Variance:

 

How can I build a table that would show EBITDA as?:

[parry2k]

@Anonymous I think it should be something like this, add the following measure and put in matrix visual.

Base Sum = SUM ( Table[Amount] )

Actual = CALCULATE ( [Base Sum], Table[Scenario] = "Actual" )

Budget = CALCULATE ( [Base Sum], Table[Scenario] = "Budget" )

Variance = [Actual] - [Budget]

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

@parry2k , thank you for the reply.

I modified your solution to get the answer.

I am looking for EBITDA number which is Revenue - Expense (sum of COR and SGA) .

My equation:

Actual EBITDA = calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Actual"))

Budget EBITDA = calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Budget"))

EBITDA Variance = [Actual EBITDA]-[Budget EBITDA]

This gets me the result. The only problem is that if I have to estimate the variance for Revenue,COR and SGA separately I would have to write 9 more equations. Is there an easy way to get all the variance analysis with minimum coding?

[v-chuncz-msft]

@Anonymous 

You may try ISINSCOPE to change the total.

• https://community.powerbi.com/t5/Desktop/Dynamic-Difference-Measure-Using-Slicer/m-p/590745#M280280
• https://community.powerbi.com/t5/Desktop/Difference-between-two-columns-in-matrix-visualisation/m-p/...