On Every Drill down on rows formula to change

[pavansada08143]

Am trying to calculate this measure and currently it is giving correct answer. however i noticed one thing. in the below measure if i replace all the SUM(StockOnHand[Qty]) with variable SalesForEachMaterial it doest not give correct answer

InterMediateStep =

VAR CheckIf_FilterAppliedOnMaterial = HASONEVALUE(StockOnHand[Material]) && NOT(ISFILTERED(StockOnHand[Grid]))

VAR CheckIf_FilterAppliedOnGrid = HASONEVALUE(StockOnHand[Grid]) && ISFILTERED(StockOnHand[Grid])

VAR SalesForEachMaterial = SUM(StockOnHand[Qty])

RETURN

SWITCH(

    TRUE(),

    CheckIf_FilterAppliedOnMaterial=TRUE(),

    DIVIDE(

        SalesForEachMaterial,

        [TotalOfSalesValueByStyle]),

    CheckIf_FilterAppliedOnGrid=TRUE(),

    DIVIDE(

        SUM(StockOnHand[Qty]),

        CALCULATE(

            SUM(StockOnHand[Qty]),

            ALLSELECTED(StockOnHand[Grid]))),

    [StyleContributionToTotalSales])

[d_gosbell · Accepted solution]

---

@pavansada08143 wrote:

... if i replace all the SUM(StockOnHand[Qty]) with variable SalesForEachMaterial it doest not give correct answer

---

If you replace ALL the references it definitely not give you the same answer as keeping the SUM(StockOnHand[Qty]) references inline. This is because variables are evaluated once in the scope in which they are declared and the value for them is stored. The second time you reference this expression it is inside a CALCULATE() call, the CALCULATE will not have any effect on the value of the variable as the value for it has already been stored so changing the filter context does not do anything. 

So you would either need to leave that second reference in place or create an additional variable for the AllSelected version of "SalesForEachMaterial" that has the result of the calculate statement 

eg.

InterMediateStep =

VAR CheckIf_FilterAppliedOnMaterial = HASONEVALUE(StockOnHand[Material]) && NOT(ISFILTERED(StockOnHand[Grid]))

VAR CheckIf_FilterAppliedOnGrid = HASONEVALUE(StockOnHand[Grid]) && ISFILTERED(StockOnHand[Grid])

VAR SalesForEachMaterial = SUM(StockOnHand[Qty])
VAR SalesForEachMaterialAllSelected =

        CALCULATE(

            SUM(StockOnHand[Qty]),

            ALLSELECTED(StockOnHand[Grid]))

RETURN

SWITCH(

    TRUE(),

    CheckIf_FilterAppliedOnMaterial=TRUE(),

    DIVIDE(

        SalesForEachMaterial,

        [TotalOfSalesValueByStyle]),

    CheckIf_FilterAppliedOnGrid=TRUE(),

    DIVIDE(

        SalesForEachMaterial,

        SalesForEachMaterialAllSelected),

    [StyleContributionToTotalSales])

[pavansada08143]

Thats suggestion worked out well. however i have encountered another problem during my drill up. At the heighest level i am finding incorrect values. Meaning At the 1st level i am trying to do the Style level total against Grand total.

InterMediateStep =

VAR CheckIf_FilterAppliedOnMaterial = HASONEVALUE(StockOnHand[Material]) && NOT(ISFILTERED(StockOnHand[Grid]))

VAR CheckIf_FilterAppliedOnGrid = HASONEVALUE(StockOnHand[Grid]) && ISFILTERED(StockOnHand[Grid])

VAR SalesForEachMaterial = SUM(StockOnHand[Qty])
VAR SalesForEachMaterialAllSelected =

        CALCULATE(

            SUM(StockOnHand[Qty]),

            ALLSELECTED(StockOnHand[Grid]))

RETURN

SWITCH(

    TRUE(),

    CheckIf_FilterAppliedOnMaterial=TRUE(),

    DIVIDE(

        SalesForEachMaterial,

        [TotalOfSalesValueByStyle]),

    CheckIf_FilterAppliedOnGrid=TRUE(),

    DIVIDE(

        SalesForEachMaterial,

        SalesForEachMaterialAllSelected),

    [StyleContributionToTotalSales])

This part is giving different result when used in another measure and gives different result when used directly. in the attached image green results are expected.

[pavansada08143]

Finally i was able to pass through the hickups and get the output as expected by using below measure.

InterMediateStep =

//Using this variable to check how many materials are visible in the 2nd level of drill down approach

VAR CheckOnHowManyMaterialsAvailableInEachStyle =

CALCULATE(

COUNTROWS(VALUES(StockOnHand[Material])),

ALLSELECTED(StockOnHand[Material]))

//Using this variable to check if the values is getting computing at the material level (2nd level)

VAR CheckIf_FilterAppliedOnMaterial = HASONEVALUE(StockOnHand[Material]) && NOT(ISFILTERED(StockOnHand[Grid]))

//Using this variable to check if the values is getting computing at the Grid level of each material (3rd level)

VAR CheckIf_FilterAppliedOnGrid = HASONEVALUE(StockOnHand[Grid]) && ISFILTERED(StockOnHand[Grid])

//Using this variable to consider zero sales if returns are excess than sales and also UNDERSTOOD that since veriables are computed once for the declared scope later if we use it inside the another calculate function by changing another filter context this will no longer be valid as it is not getting RE-EVALUATED.

VAR SalesForEachItem = IF(SUM(StockOnHand[Qty])<0,0,SUM(StockOnHand[Qty]))

//Using this variable to store Material level sales when grids are visible

VAR SalesForEachGridToDisplayMaterialSales =

CALCULATE(

IF(SUM(StockOnHand[Qty])<0,0,SUM(StockOnHand[Qty])),

ALLSELECTED(StockOnHand[Grid]))

//Using this variable to store Style level sales when materials are visible

VAR SalesForEachMaterialToDisplayStyleSales =

CALCULATE(

IF(SUM(StockOnHand[Qty])<0,0,SUM(StockOnHand[Qty])),

ALLSELECTED(StockOnHand[Material])

)

//Using this variable to store GrandTotal level sales when styles are visible

VAR SalesForEachStyleToGrandTotalSales =

CALCULATE(

IF(SUM(StockOnHand[Qty])<0,0,SUM(StockOnHand[Qty])),

ALL(StockOnHand[Style])

)

RETURN

SWITCH(

TRUE(),

CheckIf_FilterAppliedOnGrid=TRUE(),

DIVIDE(

SalesForEachItem,

SalesForEachGridToDisplayMaterialSales,0),

AND(CheckIf_FilterAppliedOnMaterial=TRUE(),CheckOnHowManyMaterialsAvailableInEachStyle> 1),

DIVIDE(

SalesForEachGridToDisplayMaterialSales,

SalesForEachMaterialToDisplayStyleSales,0),

AND(CheckIf_FilterAppliedOnMaterial=TRUE(),CheckOnHowManyMaterialsAvailableInEachStyle= 1),

[StyleContributionToTotalSales],

DIVIDE(

SalesForEachMaterialToDisplayStyleSales,

SalesForEachStyleToGrandTotalSales,0))