Forum Discussion
Filter on Aggregation
Hi POWER BI Team, I need your help on a project and I will try my best to explain the desired outcome. My company is structured into 3 Business Units (BUs) - BU A, BU B, BU C, and we have a...
Hi v-yingjl ,
Please see below
<< Based on your description, I want to confirm something based on my sample:
- No matter which BU has been selected, all of values <=3 should be ignored to calcualte average - NO, value <= 3 should be removed from calculation only for BU A.
- If all BUs are selected, just ignore values <=3 in BU A ,other BU values <=3 should be retained to calcualte average - YES - Average should include all values except <= 3 for BU A.
- When select BU A, all of values <=3 should be ignored to calcualte average - YES
- If no BU is selected, all of values <=3 should be ignored to calcualte average - NO, all values should be included in the average but only <=3 value should be excluded for BU A.
I hope this helps.
Thank you.
Hi corange ,
If only single or all slicers are considered to be selected, I think the below measure should work:
Measure =
VAR _avg =
CALCULATE (
AVERAGEX (
FILTER ( 'Table', 'Table'[value] >= 3 || 'Table'[BUs] <> "BU A" ),
'Table'[value]
),
ALLEXCEPT ( 'Table', 'Table'[BUs], 'Table'[state] )
)
VAR _avgA =
CALCULATE (
AVERAGEX (
FILTER ( 'Table', NOT ( 'Table'[value] < 3 && 'Table'[BUs] = "BU A" ) ),
'Table'[value]
),
ALLEXCEPT ( 'Table', 'Table'[BUs], 'Table'[state] )
)
VAR _avgother =
AVERAGE ( 'Table'[value] )
RETURN
IF (
HASONEFILTER ( 'Table'[BUs] ),
IF (
NOT ( ISFILTERED ( 'Table'[BUs] ) ),
_avg,
IF ( SELECTEDVALUE ( 'Table'[BUs] ) = "BU A", _avgA, _avgother )
),
_avg
)
Attached sample file: Filter on Aggreation.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.