Page 1 of 1

SUMX Giving Incorrect Grand Total

Posted: Sun Sep 09, 2018 1:57 am
by smjzahid
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.

Re: SUMX Giving Incorrect Grand Total

Posted: Sun Sep 09, 2018 8:08 am
by MattAllington
I can help you, but I can’t give an exact answer from the information you have provided.

Read my article explaining SUM vs SUMX. The exact formula to need depends on the data model. Eg I can’t be sure if CY MTD and PY MTD are measures or columns, or if there are any other tables, or if CBU comes from the vw_DSF table. But something like this should work depending on the tables and assuming CY and MY are columns.

SUMX(table[cbu], CALCULATE(sum(table[CY MTD])/sum(table[PY MTD])))

Also read my Best Practices in Power BI. You should give your table a better more descriptive name.

Re: SUMX Giving Incorrect Grand Total

Posted: Mon Sep 10, 2018 3:22 am
by smjzahid
Hi Matt
Thank you for your reply, I have read your blog on difference between SUM and SUMX (very informative), I did tried to apply the suggestion below but as you rightly mention that you are proposing a solution without complete understanding of the data model.

Here are the details on the data model. The data model is based on a single view, and the DDL used to create the view named vew_dsf_final is attached for your perusal.

CYMTD ----> This is a computed column in the source table and uses a formula for eg:
rpt_daily_sales_flash_v2.cy_mtd_salesvaluegbp / (1000)

PYMTD-----> This is a computed column in the source table and uses a formula for eg:
rpt_daily_sales_flash_v2.py_mtd_salesvaluegbp / (1000)

CBU------> rpt_daily_sales_flash_v2.cbu_group AS cbu

Please see the image for the data model in Power BI attached as well. Please let me know if you need further information.

Many Thanks

Re: SUMX Giving Incorrect Grand Total

Posted: Mon Sep 10, 2018 6:16 am
by MattAllington
My formula had an error, but was right for the model. Just swap out the table name with yours below.

SUMX(values(table[cbu]), CALCULATE(sum(table[CY MTD])/sum(table[PY MTD])))

Re: SUMX Giving Incorrect Grand Total

Posted: Mon Sep 10, 2018 6:45 pm
by smjzahid
Hi Matt

Yes I have tried your new suggestion. However, I get an error as I type in the formula in Power BI (See below is the actual error I copied from Power BI and pasted here)

Code: Select all

The%20syntax%20for%20')'%20is%20incorrect.%20(DAX(%20sumx(values(vw_dsf_final[cbu]),CALCULATE(SUM(vw_dsf_final[cy%20mtd]/SUM(vw_dsf_final[py%20mtd])))%20//%20SUMX(values(table[cbu]),%20CALCULATE(sum(table[CY%20MTD])/sum(table[PY%20MTD])))%20)))))


I am assuming the table you have referred to in your formula is the actual view in my case as I am using that single view in the model to create this report.

Re: SUMX Giving Incorrect Grand Total

Posted: Mon Sep 10, 2018 7:08 pm
by MattAllington
Sorry, I can’t tell from what you have pasted what the issue is. Can you just paste the exact formul you are typing?

Re: SUMX Giving Incorrect Grand Total

Posted: Tue Sep 11, 2018 6:25 am
by smjzahid
Sorry about that, Here is it is

sumx(values(vw_dsf_final[cbu]),CALCULATE(SUM(vw_dsf_final[cy mtd]/SUM(vw_dsf_final[py mtd]))))

Re: SUMX Giving Incorrect Grand Total

Posted: Tue Sep 11, 2018 6:47 am
by MattAllington
The formula you have pasted is not the one I provided. You are missing a close bracket before the divide and you have an extra one at the end

Re: SUMX Giving Incorrect Grand Total

Posted: Thu Sep 13, 2018 1:43 am
by smjzahid
Hi Matt

Yes you were right (as usual :) ) It was my mistake not to close the bracket properly, Your formula has worked as far as I am concerned. However, I have asked my Finance guys to cross check the figure in the Power BI report until then let us assume that it is correct.

I have a second query though please see the explanation below.

I am using a filter on a report. This filter is a column in the view which has 2 distinct values (Core Brand and Core CBU) see the image attached to understand it better. I am using a Table viz in my report in power BI Desktop, I want the header of the table to change as per the slicer selected by the user for eg:
If user selects Core Brands on a slicer then the highlighted part in the attachment should say CORE BRAND rather than cbu.

Many thanks for your help Matt.

Re: SUMX Giving Incorrect Grand Total

Posted: Mon Sep 17, 2018 7:37 am
by MattAllington
It is currently not possible to change the headings in a table based on a slicer. You could write a new measure and put it in a card above the heading if you like. something like this (you will have to change the table and column names)

=SELECTEDVALUE(Table[C2])