cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Using a slicer value across a table within a measure

Hi All,

I have a table I'm using in my reports that looks like the below:

 Brand Name Average Sold Price Brand Variance (£) Company 1 200,000 -200,000 Company 2 500,000 100,000

I also have a slicer that uses the same Brand Name field that is a dropdown where you can select a value to change based on that Brand Name.

I also have an Average Sold Price of Brand measure that works out that Comanies Average Sold Price.

So for example with the table above. This is how I would want it to look if the Brand I selected in the slicers average Sold Price was 400,000. So the variance in the table needs to reflect how it does above with it dynamically working out the variance column based on the slicer. I need to be able to use the value within the Average Sold Price of Brand measure across all of the rows.

Currently I am seeing it as this due to it filtering on each of the rows:

 Brand Name Average Sold Price Brand Variance (£) Company 1 200,000 0 Company 2 500,000 0

In summary I need to be able to use a filtered (by slicer) value within a measure across all rows in this table.

Does anyone know how I can achieve this? Happy to provide more information if any is required.

1 ACCEPTED SOLUTION
Community Support

Hi @charliep938 ,

Here I suggest you to create an unrelated "Dim Brand Name" table for slicer.

My Sample:

Data model:

Measure:

Average Sold Price = CALCULATE(AVERAGE('Table'[Sold Price]))
Brand Variance (£) =
VAR _SELECTBRAND =
SELECTEDVALUE ( 'Dim Brand Name'[Brand Name] )
VAR _SELECTAVG =
CALCULATE ( [Average Sold Price], 'Table'[Brand Name] = _SELECTBRAND )
RETURN
[Average Sold Price] - _SELECTAVG

Result is as below.

Best Regards,
Rico Zhou

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

2 REPLIES 2
Frequent Visitor

Thanks @v-rzhou-msft that work's perfectly as a solution!

Community Support

Hi @charliep938 ,

Here I suggest you to create an unrelated "Dim Brand Name" table for slicer.

My Sample:

Data model:

Measure:

Average Sold Price = CALCULATE(AVERAGE('Table'[Sold Price]))
Brand Variance (£) =
VAR _SELECTBRAND =
SELECTEDVALUE ( 'Dim Brand Name'[Brand Name] )
VAR _SELECTAVG =
CALCULATE ( [Average Sold Price], 'Table'[Brand Name] = _SELECTBRAND )
RETURN
[Average Sold Price] - _SELECTAVG

Result is as below.

Best Regards,
Rico Zhou

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.