Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dc189
Helper II
Helper II

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
v-caliao-msft
Employee
Employee

@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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.