How to use CALCULATE SUM ALL with related columns

[mohammedmahadik]

I have two tables in PowerBI. SOV_SOT has Values, Brand, Date. The other BRAND_Master has Brand_O/P and Brand_SOV. I have created a relation between columns "Brand" from SOV_SOT table and column "BRAND_SOV" in the BRAND_Master table.
It is a Many to One relationship. I use the BRAND_O/P to create a slicer to select brand. I want to have a calculate sum formula such that it has an all filter. but the selection should be basis BRAND_O/P

The following formula doesn't work and if I change ALL argument to SOV_SOT[BRAND], it doesn't give me the required result.

Calc_SOT =

    CALCULATE(

    SUM(SOV_SOT[TOTAL TIME IN MIN(Sum)]),

    ALL(BRAND_Master[BRAND_O/P])

    )

[v-xinruzhu-msft]

Hi @mohammedmahadik 

Try the following measure.

Calc_SOT =
IF (
    CALCULATE ( SUM ( SOV_SOT[TOTAL TIME IN MIN(Sum)] ) ) <> BLANK (),
    CALCULATE ( SUM ( SOV_SOT[TOTAL TIME IN MIN(Sum)] ), ALLSELECTED ( SOV_SOT ) )
)

Best Regards!

Yolo Zhu

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

[mohammedmahadik · Accepted solution]

Thank you all who helped.

I figured it our eventually.

Calc_SOT = CALCULATE( SUM(SOV_SOT[TOTAL TIME IN MIN(Sum)]), ALL(SOV_SOT[BRAND]), REMOVEFILTERS(BRAND_MASTER[BRAND_O/P]))

[amitchandak]

@mohammedmahadik , You need all, if you want ignore the filter. FIlter should work on measure like

Calc_SOT = SUM(SOV_SOT[TOTAL TIME IN MIN(Sum)])

Or use allselcetd

Calc_SOT =
CALCULATE(
SUM(SOV_SOT[TOTAL TIME IN MIN(Sum)]),
ALLSELECTED(BRAND_Master)
)

or

Calc_SOT =
CALCULATE(
SUM(SOV_SOT[TOTAL TIME IN MIN(Sum)]),
ALLSELECTED(BRAND_Master),
VALUES(BRAND_Master[BRAND_SOV])
)

[mohammedmahadik]

Hey Amit,

It didn't work.