Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Column Total does not match - Part 2

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

 

"Calculated Column"

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.