Help Subtotal ISINSCOPE

[Jasorami]

Hello, I am creating a Price volume Mix analysis in PowerBI , with the correct subtotal values at each level using ISINSCOPE, but I have some error 😞

Thanks in advance for your help 🙂

Scenario: Actual vs Budget

Formulas

Price:

= IF(SUM('DB'[2022 Bud USD])=0,0,

IF(SUM('DB'[2022 Act USD])=0,0,

([Avg Price Act]-[Avg Price Bud])*SUM('DB'[2022 Act Qty])))

Mix:

=IF(SUM('DB'[2022 Bud USD])=0,0,

IF(SUM('DB'[2022 Act USD])=0,0,

[2022 Tot Inv]*([Act % Qty]-[Bdg % Qty])*([Avg Price Bud]-[2022 Tot Avg Prc Bdg])))

Vol:

=IF(SUM('DB'[2022 Bud USD])=0,[Bdg Change],

IF(SUM('DB'[2022 Act USD])=0,[Bdg Change],

(sum('DB'[2022 Act Qty])-sum('DB'[2022 Bud Qty]))*[Avg Price Bud])-[Mix])

The results of the previous formulas are ok, for each sku.

But when I am traying to subtotalize in a hierarchy level using ISINSCOPE with an additional meassure, the subtotal value for price is ok,, but for Mix and Vol the subtotals are incorrect:

What I have wrong in the Mix and Vol formulas?? 😞

Price:

Pric2 Bdg =

SWITCH(

TRUE(),

ISINSCOPE('DB'[Material (BE #)]),[PVM Pric Bdg],

ISINSCOPE('DB'[Product hierarchy level 4])||ISINSCOPE('DB'[Product hierarchy level 3])||

ISINSCOPE('DB'[Product hierarchy level 2])||ISINSCOPE('DB'[Product hierarchy level 1])||

ISINSCOPE('DB'[Application Field])||ISINSCOPE('DB'[Market Field])||

ISINSCOPE('DB'[Company Name]),

SUMX(VALUES('DB'[Material (BE #)]),[PVM Pric Bdg]))

Mix:

Mix2 Bdg =

SWITCH(

TRUE(),

ISINSCOPE('DB'[Material (BE #)]),[PVM Mix Bdg],

ISINSCOPE('DB'[Product hierarchy level 4])||ISINSCOPE('DB'[Product hierarchy level 3])||

ISINSCOPE('DB'[Product hierarchy level 2])||ISINSCOPE('DB'[Product hierarchy level 1])||

ISINSCOPE('DB'[Application Field])||ISINSCOPE('DB'[Market Field])||

ISINSCOPE('DB'[Company Name]),

SUMX(VALUES('DB'[Material (BE #)]),[PVM Mix Bdg]))

Vol:

Vol2 Bdg =

SWITCH(

TRUE(),

ISINSCOPE('DB'[Material (BE #)]),[PVM Vol Bdg],

ISINSCOPE('DB'[Product hierarchy level 4])||ISINSCOPE('DB'[Product hierarchy level 3])||

ISINSCOPE('DB'[Product hierarchy level 2])||ISINSCOPE('DB'[Product hierarchy level 1])||

ISINSCOPE('DB'[Application Field])||ISINSCOPE('DB'[Market Field])||

ISINSCOPE('DB'[Company Name]),

SUMX(VALUES('DB'[Material (BE #)]),[PVM Vol Bdg]))

[v-cazheng-msft]

Hi @Jasorami ,

May I know whether your issue has been resolved? If you still have problems on it, could you please provide some sample data from your tables and let us know the relationships among them so we can try to find a solution for you? Thanks in advance!

Best Regards,

Community Support Team _ Caiyun

[littlemojopuppy]

Hi @Jasorami can you share some data?