Change calculation of a column based on a slicer

[Anonymous]

I have a scenario im trying to do but i am struggling to work out how.
My colleague has done it in Tableau, and i dont want to admit that PowerBi cant do it, it must be able to and i just dont know how.

Image below, shows on the left, what the raw data looks like.

What i want to do, is have a display like the Table on the right hand side, where the column "Period 1 Qty Sold" is calculated only for the rows that are for the PromoPeriod that is selected on the first Slicer on the left.
Then i want the column "Period 2 Qty Sold" to be calculated only for the rows that are for the PromoPeriod that is selected on the second slicer on the right.

Is this possible?

[Anonymous · Accepted solution]

I think you'll need to duplicate the promo period column (lets call it PromoPeriod2)

Have slicer 1 as PromoPeriod and slicer 2 as PromoPeriod2

Promo 1 Qty Sold = CALCULATE(SUM(Tbl[QtySold]),ALLSELECTED(Tbl[PromoPeriod]))

Promo 2 Qty Sold = CALCULATE(SUM(Tbl[QtySold]),ALLSELECTED(Tbl[PromoPeriod2]))

I think you'll need to remove the slicer relationship from your table also.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may create two slicer tables use Slicer 1 = DISTINCT(Test[PromoPeriod]) first. Then you may create the measures as below to get the value as requested.

Period 1 Qty Sold =
CALCULATE (
    SUM ( Test[QtySold] ),
    FILTER ( Test, Test[PromoPeriod] IN VALUES ( 'Slicer 1'[PromoPeriod] ) )
)

Period 2 Qty Sold =
CALCULATE (
    SUM ( Test[QtySold] ),
    FILTER ( Test, Test[PromoPeriod] IN VALUES ( 'Slicer 2'[PromoPeriod] ) )
)

Regards,

Cherie

[Anonymous]

Thank you so much! Thats exactly what i needed!!!!!

[Anonymous · Accepted solution]

I think you'll need to duplicate the promo period column (lets call it PromoPeriod2)

Have slicer 1 as PromoPeriod and slicer 2 as PromoPeriod2

Promo 1 Qty Sold = CALCULATE(SUM(Tbl[QtySold]),ALLSELECTED(Tbl[PromoPeriod]))

Promo 2 Qty Sold = CALCULATE(SUM(Tbl[QtySold]),ALLSELECTED(Tbl[PromoPeriod2]))

I think you'll need to remove the slicer relationship from your table also.