cancel
Showing results for
Did you mean:
Regular Visitor

## Showing Sales % Growth using Hasonevalue with different slicer value filters

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

2 REPLIES 2
Moderator

## Re: Showing Sales % Growth using Hasonevalue with different slicer value filters

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

Regular Visitor