topic Re: Column Total does not match - Part 2 in DAX Commands and Tips

Column Total does not match - Part 2

Anonymous — Fri, 26 Jul 2024 02:50:04 GMT

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

Re: Column Total does not match - Part 2

Anonymous — Fri, 26 Jul 2024 02:53:01 GMT

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.

Re: Column Total does not match - Part 2

Anonymous — Fri, 26 Jul 2024 07:41:41 GMT

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.

Re: Column Total does not match - Part 2

Anonymous — Mon, 29 Jul 2024 04:35:45 GMT

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.