Column Total does not match - Part 2

[Anonymous]

Hi,

This is Part 2 or continuation of my first inquiry on 'Column total does not match'

See screenshot below:

The screenshot here pertains to Purchase Price Variance showing:

1.  Material Code

2.  Material Description

3.  Unit price per LY = see new measure: 

Net Price per Unit LY = CALCULATE(AVERAGE('me2l'[Net Price per Unit]),DATEADD('DATE TAB'[Date],-1,YEAR))

4. Unit price per CY = see new measure: 

Net Price per Unit = DIVIDE([PO Php Vat Ex],[Order Qty-adj],0)

5. Order Qty-adj is as per source data or sum of ordered quantity for the specified date range for the current year.

6. PPV TOTAL = see new measure: 

PPV TOTAL = (SUMX(values('me2l'[Material]),[PPV Unit Price 2])), 

where there is another new measure link to this, see below:

PPV Unit Price 2 = CALCULATE(ROUND(AVERAGE([Net Price per Unit])-[Net Price per Unit LY],2))*sum('me2l'[Order Qty-adj])

But as we can see in the two tables above, as we removed the sum format in "Order Qty-adj", that the column total of PPV TOTAL changes as well.  Hence, the PPV TOTAL's column total is affected by the totals of the other columns.

What DAX should I use to just simply get the total of the column PPV TOTAL?

Need your help please since currently i removed the totals in the table.  See below:

Looking forward for a solution, please. thanks in advance!

All the best

[Anonymous]

Hi @Anonymous 

The change of the sum format in "Order Qty-adj" will change the row number and some variable of the table visual.

This is correlative to the context DAX:

Context in DAX Formulas - Microsoft Support

Please try this:

MEASURE =
VAR _vtable =
    SUMMARIZE (
        ALLSELECTED ( 'me2l' ),
        'me2l'[Material],
        'me2l'[Short Text],
        "_UP Per LY", [Unit Price per LY],
        "_UP per CY", [Unit Price per CY],
        "_Order Qty-adj", SUM ( 'me2l'[Order Qty-adj] ),
        "_PPV Total", [PPV Total]
    )
RETURN
    IF (
        HASONEVALUE ( 'me2l'[Material] ),
        SUMX (
            FILTER (
                _vtable,
                [Material] = SELECTEDVALUE ( 'me2l'[Material] )
                    && [Short Text] = SELECTEDVALUE ( 'me2l'[Short Text] )
            ),
            [__PPV Total]
        ),
        SUMX ( _vtable, [__PPV Total] )
    )

Best Regards

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

[Anonymous]

Hi Zhengdong Xu,

Thank you for taking time. Appreciate it!

I have not used this measure you sent.  thanks again.

Please see result:

The Total PPV should be -P21,992,537.08 (per Should be in Excel) but the one in the dashboard in Total PPV is -P21,990,900.67 , while the MEASURE has a total of P496,193,884.98.

See downloaded in Excel with Total PPV and Should Be Totals

Looking forward for your response and solution.

[Anonymous]

Purchase Price Variance is computed as ff: 

Unit price CY less Unit price LY x Order Qty = if total value is negative, its a savings; if the total value is positive, its a negative savings.  FYI.