How to use a measure or SELECTEDVALUE of a slicer in a summarized table?

[CesarF_mx]

Hello,

I'm a newbie in PowerBI and I really need your help to do a summarization with a slider

1) Asume a Source table with date, product and sales columns

Date       Product Sales
1/1/24   A    41
2/1/24   B    42
3/1/24   C    43
1/1/23   A    31
2/1/24   B    32
3/1/25   C    33
1/1/22   A    21
2/1/22   B    22
3/1/22   C    23
1/1/21   A    11
2/1/21   B    12
3/1/21   C    13

2) Also a slicer with all years from Source

Slicer Value:

2024

3) Then a measure that is SELECTEDVALUE(Source[Year])

Measure Value:

2024

4) I created a virtual table summarizing sales per year

Year Sales:

2024 126

2023 96

2022 66

2021 36

5) Now I need to compare sales from each year against the sales of selected year

Desired Result:

Year  Sales  Difference            <Calc:>
2024  126            0%             <(126/126) -1>
2023  96            31%             <(126/ 96) -1>
2022  66            91%             <(126/ 66) -1>
2021  36           282%             <(126/ 36) -1>

6) Problem is how to use the measure or SELECTEDVALUE to identify the summarized sales and use it in a DIVIDE? 

Thank you for your help!

[CesarF_mx]

Hi @suparnababu8 and @MFelix,

Thank you both for your help—I really appreciate it. After studying several tutorials, I believe I now understand why I'm stuck.

My goal is to create a calculated table that includes all three columns at once, rather than using measures. This is important because I'll need to perform around 350 calculations for a client dashboard, and this model was just a preliminary attempt to seek guidance.

Am I correct in thinking that this could be achieved using variables in a calculated table definition? If so, could you guide me on how to transition from using measures to variables instead?

Again, thank you both for your assistance.

[Anonymous · Accepted solution]

Thanks for the reply from MFelix , please allow me to provide another insight:
Hi,@CesarF_mx 
Thanks for letting us know your user experience.  According to your case description, I do understand how frustrated you are now.

At the moment, there are no dax functions that can get the value of the slicer in the calculated table and calculated columns,I feel regretful to inform you that it turns out to be a by-design one. so your needs are not available for the time being:

But I have another alternative for you:

1.To get started, you can create a measure like this:

Difference 2021 = 
VAR ll1=CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),YEAR('Table'[Date])=2021))
RETURN 1-DIVIDE(SUM('Table'[Sales]),ll1)

Difference 2022 = 
VAR ll1=CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),YEAR('Table'[Date])=2022))
RETURN 1-DIVIDE(SUM('Table'[Sales]),ll1)

Difference 2023 = 
VAR ll1=CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),YEAR('Table'[Date])=2023))
RETURN 1-DIVIDE(SUM('Table'[Sales]),ll1)

And so on.

2.Next, select the value of the generation parameter and set it as a parameter:

3.Then, modify the visualization:

4.Here's my final result, which I hope meets your requirements.

If you feel that performance needs to be improved, you can also submit an idea on

https://ideas.fabric.microsoft.com/ and wait for users with the same needs as you to vote for you and help you realize the idea as soon as possible. Many features of our current products are designed and upgraded bed on customers’ feedback.  With requirements like this increase, the problem may well be released in the future.

Thanks for your understanding.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @CesarF_mx ,

Believe that you have an incorrection on your example since you have data for 2025 and the total value for 2024 is 158:

Just do the following:

• Add a column of year to your Data table:

• Add the following measures:

Total Sales = SUM(Data[Sales])

Difference = VAR basevalue = SELECTEDVALUE('Year'[Year])
		RETURN
			DIVIDE(
				CALCULATE(
					[Total Sales],
					REMOVEFILTERS(Data[Year]),
					Data[Year] = basevalue
				),
                [Total Sales]
			) - 1

Now use the column year from the data to create your visual:

See PBIX file attach