Showing Sales % Growth using Hasonevalue with different slicer value filters

[dc189]

Hello,

I have a chart wherein I want to show the Sales % growth by Channel (imagine a bar chart with % growth on Y axis and Online, Retail and Omni-channel on X axis). The % growth should change based on what I select in 1 slicer (Year). I have the formula below that works for me:

This Year Sales = if(HASONEVALUE(SmChannel[Transaction Year]),CALCULATE(SUM(SmChannel[Sales Amount])))

Last Year Sales = if(HASONEVALUE(SmChannel[Transaction Year]),CALCULATE (
SUM ( SmChannel[Sales Amount] ),
FILTER (ALLEXCEPT(SmChannel,SmChannel[Channel]), AND(SmChannel[Transaction Year]<>2015,
SmChannel[Transaction Year]
= MAX (SmChannel[Transaction Year]) -1))))

YoY Sales Growth = ([This Year Sales]-[Last Year Sales])/[Last Year Sales]

Now the issue is, I have 2 slicers Year and Region and when I filter in them, the % sales growth should update accordingly. I used the following formulae, but they don't work for me. Could someone help me correct these formulae below?

This Year Sales = if(and(HASONEVALUE(SmChannel[Transaction Year]),HASONEVALUE(SmChannel[Region])),CALCULATE(SUM(SmChannel[Sales Amount])))

Last Year Sales = if(and(HASONEVALUE(SmChannel[Transaction Year]),HASONEVALUE(SmChannel[Region])),CALCULATE (
SUM ( SmChannel[Sales Amount] ),
FILTER (ALLEXCEPT(SmChannel,SmChannel[Channel]), AND(SmChannel[Transaction Year]<>2015,
SmChannel[Transaction Year]
= MAX (SmChannel[Transaction Year]) -1)
)))

YoY Sales Growth = if(and(HASONEVALUE(SmChannel[Transaction Year]),HASONEVALUE(SmChannel[Region])),([This Year Sales]-[Last Year Sales])/[Last Year Sales])

Thank you very much in advance,

dc189

[v-caliao-msft]

@dc189,

In your scenario, why do you need to use Hasonevalue function? A slicer narrows the portion of the dataset shown in the other visualizations. When no items selected in your slicers, measure will be calcualted  for all the dataset. If you selected some items in Year and Region slicer, the measue will be caluated for the corresponding Years and Regions.

If this is not what you want, please provide us some sample data, and elaborate your expected result, so that we can make further analysis.

Regards,

Charlie Liao

[dc189]

Hi @v-caliao-msft,

Please see link to a sample pbix file: https://www.dropbox.com/s/7jhn76vl5n7v67a/Channel%20Data.pbix?dl=0

Ultimately, I want the user to be able to filter the chart by Year, Region and State and see the growth in online/retail/omni-channel sales for any combination of selected filters. The problem I'm facing now is the chart shows correct values only when Year is filtered but when I filter both Year and Region, the chart shows incorrect values.

How do I correct those values?

Thanks.