cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yvonnedlr Frequent Visitor
Frequent Visitor

Trying to calculate percentage difference

 

I have a matrix which has a number of groups down the side and also across the top and under each of the top groups there is a total of sales for each year, in this instance it is 2018 and 2019. I am trying to add a 3rd column alongside 2018 and 2019 which calculates the percentage difference between 2018 and 2019.  I just cannot figure out how to do this.

I am really new to Power BI so any assistance would be appreciated

3 REPLIES 3
Super User
Super User

Re: Trying to calculate percentage difference

If M is the measure, and the model has a proper Date table, then:

 

 

[M YoY %] =
var __current = [M]
var __today = today()
-- __effectiveDates limits the
-- current period to only (the
-- past + today) since comparing
-- the whole of 2019 to the whole of
-- 2018 means you are comparing the past
-- and some of the future to the past
-- which is obviously misleading. If
-- today is 1 June, say, then you want
-- to compare 2019 against 2018 but only
-- up to 1 June. Right? if this is not
-- what you want, then remove __effectiveDates
-- and in __prevYear replace the condition
-- by sameperiodlastyear( Dates[Date] )
var __effectiveDates =
	calculatetable (
		values ( Dates[Date] ),
		Dates[Date] <= __today
	)
var __prevYear =
	calculate(
		[M],
		-- Dates is the proper Date table that
		-- is connected to your sales fact table.
		sameperiodlastyear ( __effectiveDates )
	)
var __yoy =
	divide(
		__current - __prevYear,
		__prevYear
	)
return
	__yoy

Best

Darek

yvonnedlr Frequent Visitor
Frequent Visitor

Re: Trying to calculate percentage difference

Thanks, I will give that a try and let you know how I go

 

Yvonne

yvonnedlr Frequent Visitor
Frequent Visitor

Re: Trying to calculate percentage difference

I have had a go at this but it is not working for me. Clearly I am doing something wrong. Not experienced enough to pick up what is wrong. This picture reflects what I am trying to achieve, the difference between 2018 and 2019. I keep on getting a column after each column. Just one need after each group

 

pbi.jpg

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 48 members 1,220 guests
Please welcome our newest community members: