### SUMX Giving Incorrect Grand Total

Posted:

**Sun Sep 09, 2018 1:57 am**Hi All

I am using Power Bi Desktop and via Import connection method connecting to a view on Amazon Redshift. I am using the table visual to show the data on power BI Desktop.

I am new to DAX, I have created a SUMX function as per below requirement. What I want is the grand total to appear as -16.8 which comes from this formula ((CY MTD/PY MTD)-1)*100 (Please refer to the attachment titled Excel Total.jpg)

However, when I use Power BI using this SUMX formula on a table visual below, It does not give me what I am intending, (see the attachment titled PBI.JPG) Yes I can understand that Power Bi is summing the entire column. However, the requirment is such that it should show the Grand Total as per above formula or ------> (6065/7293-1*100) = -16.83 (This is the answer I want to appear in Power BI using below formula, I can I get this work please let me know experts

How do I tweak this formula to give me the grand total as -16.8, or what could be the other work around may be using a different approach to achieve the result.

Please note the rest of the row total is correct with this formula it is only the grand total I want to appear as -16.8

MTD Var % = SUMX(vw_DSF_Final,vw_DSF_Final[CY MTD]/vw_DSF_Final[PY MTD]-1)*100

Note: I have tried to past the image of 2 attached files here, It is not working for me so I have attached the file for your reference.

I am using Power Bi Desktop and via Import connection method connecting to a view on Amazon Redshift. I am using the table visual to show the data on power BI Desktop.

I am new to DAX, I have created a SUMX function as per below requirement. What I want is the grand total to appear as -16.8 which comes from this formula ((CY MTD/PY MTD)-1)*100 (Please refer to the attachment titled Excel Total.jpg)

However, when I use Power BI using this SUMX formula on a table visual below, It does not give me what I am intending, (see the attachment titled PBI.JPG) Yes I can understand that Power Bi is summing the entire column. However, the requirment is such that it should show the Grand Total as per above formula or ------> (6065/7293-1*100) = -16.83 (This is the answer I want to appear in Power BI using below formula, I can I get this work please let me know experts

How do I tweak this formula to give me the grand total as -16.8, or what could be the other work around may be using a different approach to achieve the result.

Please note the rest of the row total is correct with this formula it is only the grand total I want to appear as -16.8

MTD Var % = SUMX(vw_DSF_Final,vw_DSF_Final[CY MTD]/vw_DSF_Final[PY MTD]-1)*100

Note: I have tried to past the image of 2 attached files here, It is not working for me so I have attached the file for your reference.