cancel
Showing results for
Search instead for
Did you mean:
Highlighted
chunkysoup 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. I 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

## 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: 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

## 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: 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

## 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

## 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 Rename column

You end up with this: 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: Final Result

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