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

YoY percentage difference

Hi all -  trying to calculate the year over year % difference for diff. products. I was able to do it before, when my data was nicely arranged by columns (FY15 Revenue, FY15 Volume, FY15 Profitability; FY16 Revenue, FY16 Volume etc), but now I unpivoted the columns and I'm not able to do that anymore. Any way to work around this issue? 

Thanks so much!

 

 BI.data.snapshot.YoY.png

1 ACCEPTED SOLUTION

Accepted Solutions
NAOS Regular Visitor
Regular Visitor

Re: YoY percentage difference

Hi,


You will need a calendar table that includes a column with the "Year" values formated as in your table.
Once you have that use that column as the axis for your visual.

A couple of mesuares like the following should do the trick.
Revenue = CALCULATE(
                                       SUM(YourTable[Value]), YourTable[Rev.Vol.Profit.GP] = "Revenue")
Revenue previous year = CALCULATE([Revenue], SAMEPERIODLASTYEAR(YourNewCalendarTable[Date]))  /*Where [Date] is the key of YourNewCalendarTable table, in other words, the column with the unique values*/

Revenue%changeYoY =
VAR RevPrevYear = [Revenue previous year]
RETURN
DIVIDE([Revenue] - RevPrevYear, RevPrevYear, Blank()) /*I just like to be explicit about that Blank value, but not really necessary*/

This should work but note this is not how it should be done. It seems to me like you should have a simple column for Revenue values and one other for each of the other attributes in the Rev.Vol.Profit.GP column. 

Let me know how it goes!

5 REPLIES 5
Community Support Team
Community Support Team

Re: YoY percentage difference

Hi @lucia_stan,

 

Could you please share your sample data and excepted result to me?

 

Regards,

Frank

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

Re: YoY percentage difference

Hi @v-frfei-msft,

 

sample.data.PNGThis is approximately how my data looks like. I want to be able to show, for instance (in a table visual), that revenue for Product Series A increased 19.3% from FY16 to FY17, but decreased by 6.4% from '17 to '18. I was able to do that before unpivoting, by using a new quick measure-percentage difference. Not sure how to do it now.

NAOS Regular Visitor
Regular Visitor

Re: YoY percentage difference

Hi,


You will need a calendar table that includes a column with the "Year" values formated as in your table.
Once you have that use that column as the axis for your visual.

A couple of mesuares like the following should do the trick.
Revenue = CALCULATE(
                                       SUM(YourTable[Value]), YourTable[Rev.Vol.Profit.GP] = "Revenue")
Revenue previous year = CALCULATE([Revenue], SAMEPERIODLASTYEAR(YourNewCalendarTable[Date]))  /*Where [Date] is the key of YourNewCalendarTable table, in other words, the column with the unique values*/

Revenue%changeYoY =
VAR RevPrevYear = [Revenue previous year]
RETURN
DIVIDE([Revenue] - RevPrevYear, RevPrevYear, Blank()) /*I just like to be explicit about that Blank value, but not really necessary*/

This should work but note this is not how it should be done. It seems to me like you should have a simple column for Revenue values and one other for each of the other attributes in the Rev.Vol.Profit.GP column. 

Let me know how it goes!

lucia_stan Frequent Visitor
Frequent Visitor

Re: YoY percentage difference

Thanks, @NAOS, this worked! Also, based on your suggestion, I later managed to create separate columns for each of the attributes (usinf If statements), which makes it way easier for a newbie like me to handle work with the data and create visuals. Thanks again.

Highlighted
NAOS Regular Visitor
Regular Visitor

Re: YoY percentage difference

Hey @lucia_stan! Im glad it worked.

You could also try using the query editor and pivot the column, instead of using the "if" statements. That will generate one column for each of the different values in the column you pivot. You will have to use another column for the values. In the table posted with the original question, I imagine you will want to pivot the "Attribute" field and use the "Value" column as the values...so basically reverse the operation that created those two columns in the first place.

If you do this then you will have to change your measures, but they will be even more simple Smiley Wink