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

Year over year percentage for various products

Hello,

I have found many other similar questions and have tried the solutions contained within those but to no avail.

 

Below is my data. I would like to create a chart that shows the percentage of growth/decline of a product line year over year.

YearOverYear.jpgI am assuming I would have to create a measure for each product line individually unless DAX can lookup what is in the Values field? That is step 1001. I just need to get past Step 1 for now and create a measure that actually works. I have tried the quick measures but it just always shows as Zero and I can't even bring in the Year Column. I had to create a seperate calendar and bring that in and even then I couldn't bring in the year, I had to create days first and determine the year and then I could create the relationship but bringing into the quick measure I could only bring in days...not years.

 

I am new to measures and more than a little stumped on this as I learn all the syntax as well. Thanks ahead of time for any support you can give.  Cheers!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Year over year percentage for various products

Hi @chunkysoup,

Based on my test, you could refer to below formula:

YOY for Line1 = var a=MAX([Year])-1
var b=CALCULATE(SUM(Table1[Product Line1]),FILTER(ALL('Table1'),'Table1'[Year]=a))
return DIVIDE(b-CALCULATE(SUM(Table1[Product Line1])),CALCULATE(SUM(Table1[Product Line1])))

Result:

1.PNG

For different line, you just need to modify the formula to replace the [Product Line1]. Also you could download the pbix file to have a view.

 

Regards,

Daniel He

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

Re: Year over year percentage for various products

Hi @chunkysoup,

Based on my test, you could refer to below formula:

YOY for Line1 = var a=MAX([Year])-1
var b=CALCULATE(SUM(Table1[Product Line1]),FILTER(ALL('Table1'),'Table1'[Year]=a))
return DIVIDE(b-CALCULATE(SUM(Table1[Product Line1])),CALCULATE(SUM(Table1[Product Line1])))

Result:

1.PNG

For different line, you just need to modify the formula to replace the [Product Line1]. Also you could download the pbix file to have a view.

 

Regards,

Daniel He

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

Re: Year over year percentage for various products

Thank you so much! @v-danhe-msft I actually reversed it to show the decline. Now I just need to remove the 100% and show it as 0 otherwise all the charts look like they all decline right off the bat when a number of them grow

Gravanita Regular Visitor
Regular Visitor

Re: Year over year percentage for various products

You might want to consider an alternative solution where you won't have to create a measure for each product line.

If you unpivot your data table, things become much easier.unpivot.JPGUnpivotrelabel.JPGRename column

 

You end up with this:

dataTable.JPG

 

 

If you than create a measure like:

Measure = 
var CurYear=max(Table2[Year])
var PrevYear=CurYear-1
var curValue= CALCULATE(sum(Table2[Value]);Table2[Year]=CurYear)
var PvValue = CALCULATE(sum(Table2[Value]);Table2[Year]=PrevYear)
var ratio = DIVIDE(PvValue-curValue;curValue)
return
if(COUNTROWS(values(Table2[Year]))=1;ratio)

You'll be able to view information for all product lines like so:


finalResult.JPGFinal Result

I made changes to the previously shared pbix: Year over year percentage for various products.pbix