Re: Variance Analysis

Anonymous · ‎05-08-2020

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:

Power BI.PNG

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

parry2k · ‎05-08-2020

@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!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎05-09-2020

@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 · ‎05-14-2020

@Anonymous

You may try ISINSCOPE to change the total.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.