calculate percentage change per category

[Anonymous]

Hi,

Hoping that there is an obvious solution that I've missed here

I have a table of data that contains revenue per product, each of these products belongs to a category and I have a set of values for FY17 and FY18.

I can readily construct a % change measure which calculates the % change and can be used in a card to show the % change for each category. However what i want to be able to do is create a visual (column chart) that has each category across the bottom, shows the FY17 and FY18 values as columns and then also displays the % change between the two.

The Measure below (because it has no reference to the category) shows the same value when I include it.

MeasFY18overFY17 = DIVIDE(CALCULATE(
  SUM('Combined FY17FY18'[REVENUE]),
  FILTER( ALLSELECTED ('Combined FY17FY18'),'Combined FY17FY18'[Data Version] = 18)) - CALCULATE(
  SUM('Combined FY17FY18'[REVENUE]),
  FILTER( ALLSELECTED ('Combined FY17FY18'),'Combined FY17FY18'[Data Version] = 17)),CALCULATE(
  SUM('Combined FY17FY18'[REVENUE]),
  FILTER( ALLSELECTED ('Combined FY17FY18'),'Combined FY17FY18'[Data Version] = 17)))

 Hope this make sense!

[skasper]

Hi @Anonymous,

while this is not 100% your sceario, I think the same principle can be applied in your case. Check this video from the PowerBi team.

[Anonymous · Accepted solution]

Hi @skasper,

Thanks for the response and it has helped!  

In the sample video, the measures were done using the Time Intelligence Functions and a Date field, whereas my data is summarised up so that I only have the total projection for FY17 and the same projection for FY18.

Therefore in the measure code above, I recreated the "YTD" by filtering the table for Year = "17" and the same for "18", so my problem was not so much the % change calculation but how to get each year's revenue.

So I realised I'd been overcomplicating the measure and set up a new one

FY17Revenue = SUMX('Combined FY17FY18',IF('Combined FY17FY18'[Data Version]=17,'Combined FY17FY18'[REVENUE],0))

and similar for FY18 and therefore updated the % change.

Simon