sum of measure

[subburaj]

Hi Friends - I have an issue with calculating sum of measure. I am getting expected result when i export and see it in excel but not in power BI. VMR_MDR = [Variance MDR]*SUM(Sheet3[A Act])*6. It is working correct at row level but not in Total. I need the toal of 9209 not 11427. Kindly help.Total is not correct

[v-yulgu-msft · Accepted solution]

Hi @subburaj,

You may need these measures:

VMR_MDR = [Variance MDR]*SUM(Sheet3[A Act])*6
VMR_MDR sum up = SUMX(Sheet3,[VMR_MDR])

Add [VMR_MDR sum up] to table visual.

Best regards,

Yuliana Gu

[subburaj]

Thanks a lot

[Thejeswar]

Hi,

Are you really sure that VMR_MDR = [Variance MDR]*SUM(Sheet3[A Act])*6?

For ex, For Name C1 Nt

VMR_MDR  = -0.005% * 3659960 * 6 = -1097.9

where 

For Name Name Vo NB,

VMR_MDR = -0.010% * 2662935 * 6 =-1597.76 (The Value you show is -1669)

In that way, if you calculate for Total, the 

VMR_MDR comes as follows

= 0.002% * 117396164 * 6 =14087.539

How come you say that you should get 9209? or Am I missing something in this??

Can you clarify this?

[subburaj]

Thank you for helping me. Yes, I'm sure about VMR_MDR = [Variance MDR]*SUM(Sheet3[A Act])*6. Answering to your question on Vo NB - This difference is due to decimal, please find below with 3 decimal and 6 decimal. -1669 comes while keeping 6 decimal.

3 Decimal: VMR_MDR = -0.010% * 2662935 * 6 =-1597.76 (The Value you show is -1669)

6 Decimal: VMR_MDR = -0.010446% * 2662935 * 6 = -1669

Regarding the calculation for Total, what you have put is right but when you do total in excel, it comes to 9209 which i really need. the reason is when you do sum by row, it is coming as 9209 which is correct. Kindly help.

Providing additional informaion:

MDR_Plan = Di Plan/A Plan/6

MDR_Actual=DI Act/A Act/6

Varaince_MDR=MDR_Actaul - MDR_Plan

[Thejeswar]

Hi @subburaj,

Power BI works the way it should work.

Your Measure works the way it should work. Since you are multiplying 2 columns and a 6, even for totals that works the same way.

Instead try creating a column with the same formula. Then Display the total for the VMR_MDR.