Filtering by an unfiltered column

[R_S]

Hi,

I have a requirement where I will have a tab where people can choose a company via a slicer. That company has a region associated with it (in the same table). The slicer will be synced with all subsequent tabs. Then on a following tab I want to calculate and display some averages from all companies based on the region of the company selected from the slicer on the first page and I am not sure how to go about doing this.

Can someone offer a suggestion on how to achieve this via DAX?

Cheers,

R

[Pragati11]

Hi @R_S ,

Can you provide more details please? The requirement is not clear without any screenshots.

Thanks,

Pragati

[parry2k · Accepted solution]

@R_S add this measure

Region Avg = 
VAR __region = SELECTEDVALUE ( 'Table'[Region] )
RETURN 
CALCULATE ( 
AVERAGE ( 'Table'[Amount] ), 
ALL ( 'Table'[Company] ), 
'Table'[Region] = __region 
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[R_S]

This is great, could I take it a step further and do something like the code below (it doesn't quite work) and return a table of results?

NewTable = 
VAR __region = SELECTEDVALUE(BaseInfo[Region])
RETURN
SELECTCOLUMNS(FILTER(BaseInfo, BaseInfo[Region] = SELECTEDVALUE(BaseInfo[Region])), "Company", BaseInfo[Company], "Segment", BaseInfo[Segment], "Region", BaseInfo[Region])

[parry2k]

@R_S why you need that and what you are trying to achieve?  

[R_S]

@parry2k Just trying to learn