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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
charliep938
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 NameAverage Sold PriceBrand Variance (£)
Company 1200,000-200,000
Company 2500,000100,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 PriceBrand Variance (£)
Company 1200,0000
Company 2500,0000

 

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
Anonymous
Not applicable

Hi @charliep938 ,

 

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

My Sample:

RicoZhou_0-1677138708531.png

Data model:

RicoZhou_1-1677138740773.png

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.

RicoZhou_2-1677138792348.png

 

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.

View solution in original post

2 REPLIES 2
charliep938
Frequent Visitor

Thanks @Anonymous that work's perfectly as a solution!

Anonymous
Not applicable

Hi @charliep938 ,

 

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

My Sample:

RicoZhou_0-1677138708531.png

Data model:

RicoZhou_1-1677138740773.png

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.

RicoZhou_2-1677138792348.png

 

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.