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
orana
Helper I
Helper I

User determined variable in calculation

I'm looking to see if there is a way to take the difference between two variables  1. average sales in 2019 and 2020 for product B-A 2. sales in "x" year for product M-A, where "x" would be determined by the user looking at the dashboard (either 2019,2020,2021,2022,2023, etc.)

 

I've tried to create measures, but when I use a slicer to determine variable number 2, the slicer is also applied to variable 1. If I create a column, variables 1 and 2 are fixed, meaning I cannot change the sales year. 

 

Below is the measure I've created: 

Subtract sales avg 2019 & 2020 from Sales X year =Divide(CALCULATE(SUM('PL Portfolio'[Trace Units]), FILTER('PL Portfolio', 'PL Portfolio'[Fiscal Year] <= 2020 && 'PL Portfolio'[B & M] = "B - A")),2) - CALCULATE(sum('PL Portfolio'[Trace Units]), Filter(' PL Portfolio', 'Mckesson PL Portfolio'[B & M] = "M - A"))
1 ACCEPTED SOLUTION

Try changing the first calculate to this:

CALCULATE(
    SUM( 'PL Portfolio'[Trace Units] ),
    'PL Portfolio'[Fiscal Year] <= 2020,
    ALL( 'PL Portfolio'[Fiscal Year] ),
    'PL Portfolio'[B & M] = "B - A"
)

 

View solution in original post

6 REPLIES 6
PabloDeheza
Solution Sage
Solution Sage

Hi @orana !
I dont know your code, but it seems that adding ALL( Date[Year] ) to variable 1 could fix the problem, adding this would make your varable 1 not be affected by your Year slicer. Make sure that the column you use on your slicer is the same that you use inside ALL().
Let me know if this helps! If it is not clear enought, please make sure to provide us the code so its easier to solve the problem.

Hey @Pablo ,

 

I revised my post with the measure that I currently have. Let me know if this helps

 

Best,

Oscar

Try changing the first calculate to this:

CALCULATE(
    SUM( 'PL Portfolio'[Trace Units] ),
    'PL Portfolio'[Fiscal Year] <= 2020,
    ALL( 'PL Portfolio'[Fiscal Year] ),
    'PL Portfolio'[B & M] = "B - A"
)

 

The measure is not working. If I use my slicer and click 2021, the measure returns blank. 

Hi @orana ,

 

I was able to return the correct result using the formula from PabloDeheza . Could you please share your example data? That will help us solve the problem faster.

 

vkkfmsft_0-1652780301083.png

 

Best Regards,
Winniz

Hey,

 

I just got it to work. I was using another Fiscal Year for the slicer, which is what was driving the "blank".

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.