Incorrect Quick Measure Variance Total

Question

Hi,&nbsp;I have created a Matrix table using Customer as Rows, Year as column with Sales value as shown below:I&nbsp;create a&nbsp;Quick measure variance under Year Column as shown. However, when i check the Variance Subtotal, it is not summed up correctly. If you look at the 3rd row record, the SubTotal of Sales Amt is 788.40 + 7270 = 8058.4 which is correct. However, the subtotal variance is -6481.60 + 0 = -7270.&nbsp;&nbsp;&nbsp;Does anyone encounter the same issue that i faced?&nbsp;&nbsp;Thanks.

roytap · Answer

Just a quick point to note. Actually what i needed is the second Variance column (Column 4). However, i am unable to get rid of the first variance column (Column 2) which is why i am trying to look for alternative at the subtotal in this case.

Is there any way that i could get rid or hide the first Variance column?

Thanks

roytap · Answer

Manage to find the solution by using the suggestion provided.

https://community.powerbi.com/t5/Desktop/How-to-calculate-percentage-change-from-prior-date-prior-record/m-p/315895#M140324.

This is the workaround and it works, so its all good for me. However, i think the Quick Measure for variance total is still incorrect for some reason. Not sure whether that is a bug for this visualization.

pauldbrown · Answer

I'm not sure what your measure is supposed to compute, but from looking at the numbers in the columns, the "Variance Column" in bold on the far right appears to be the result of subtracting the "total Sales Amount" from the "2018 sales amount column".&nbsp;For the row AMANJAYA SPECIALIST CENTRE s/b Kedah:2018 sales amount &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; total sales amount &nbsp; &nbsp;&nbsp; Variance&nbsp; &nbsp; &nbsp; 4.539 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 32.232 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -27.693&nbsp;(ie: 4.539 - 32.232 = -27.693)&nbsp;&nbsp;So what exactly was the variance column supposed to compute?&nbsp;

roytap · Answer

Hi Paul,&nbsp;Currently, this is what the table show based on&nbsp;AMANJAYA SPECIALIST CENTRE s/b Kedah:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;(a)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (b)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(c)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(d)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(e)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (f)Sales 2018&nbsp; &nbsp; &nbsp;Variance&nbsp; &nbsp; &nbsp;Sales 2017&nbsp; &nbsp; Variance&nbsp; &nbsp;Total Sales&nbsp; &nbsp; Variance&nbsp; &nbsp; 4539&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 27693&nbsp; &nbsp; &nbsp; &nbsp; -23154&nbsp; &nbsp; &nbsp; &nbsp; 32232&nbsp; &nbsp; &nbsp; &nbsp; -27693&nbsp;From my understanding (d)=(c) - (a) where we will get the variance,(e)=(a) + (b) which is the total sales of 2018 and 2017,so for the total variance (f), it should be (b) + (d), but not (a)-(e) as what you stated.&nbsp;&nbsp;Is my understanding incorrect when it comes to the automatic formulation of Total and subtotal for a matrix table?

pauldbrown · Answer

roytap&nbsp;It would seem so.&nbsp;I'm curious as to what the "rogue" measure is? can you post it (with the column ids) so we can see what it is actually calculating?&nbsp;

Forum Discussion

Incorrect Quick Measure Variance Total

5 Replies

Related content

Measure returning incorrect value

Measure Total Incorrect

DAX Measure Returning Incorrect Results

Dynamic Format DAX measure calculating variance

Calculation Item giving incorrect results when 'variance' and 'variance %' measures are used