DAX Weighted Average works but does not work as expected with slicer

[Quinnie2017]

Hi - I have a DAX weighted average formula that works without a slicer.

When I add a slicer for Markets the DAX continues to calculate at the All Markets level versus the market level.

Can you help me adjust my DAX to consider the slicer?

Weighted Avg Impact =

SUMX(

SUMMARIZE('Day Key','Day Key'[KBP PD]),

DIVIDE(

CALCULATE([Avg Impact]*COUNTA(MASTER[Store]))

,

CALCULATE(COUNT('Cost Center'[Cost_CenterKey]),'Cost Center'[CC Type]="Store")

))

Exhibit 1: DAX that works at All Markets level

Exhibit 2: DAX does not adjust to only Atlanta when selecting Atlanta market (still calculates using All Markets)

[v-janeyg-msft]

Hi, @Quinnie2017 

It‘s not clear that the field on the slicer is on which table. You can try to use allselected() function in the formula involving that table.

Like this:

Weighted Avg Impact =
SUMX (
    SUMMARIZE ( ALLSELECTED ( 'Day Key' ), 'Day Key'[KBP PD] ),
    DIVIDE (
        CALCULATE ( [Avg Impact] * COUNTA ( MASTER[Store] ), ALLSELECTED ( 'MASTER' ) ),
        CALCULATE (
            COUNT ( 'Cost Center'[Cost_CenterKey] ),
            'Cost Center'[CC Type] = "Store",
            ALLSELECTED ( 'Cost Center' )
        )
    )
)

[Avg Impact] seems to be a measure, it may also need to change.

If it doesn't solve your problem, Can you share a sample file and feel free to ask me.

Best Regards

Janey Guo

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