SUMX Giving Incorrect Grand Total

Anything related to PowerPivot and DAX Formuale
smjzahid
Posts: 7
Joined: Sat Sep 08, 2018 10:19 pm

SUMX Giving Incorrect Grand Total

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.
Attachments
PBI.JPG (47.78 KiB) Viewed 2006 times
Excel Total.jpg (32.81 KiB) Viewed 2006 times

MattAllington
Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: SUMX Giving Incorrect Grand Total

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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

smjzahid
Posts: 7
Joined: Sat Sep 08, 2018 10:19 pm

Re: SUMX Giving Incorrect Grand Total

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
Attachments
PBI Data Model.JPG (37.86 KiB) Viewed 1982 times
DDL for vew_dsf_final.docx
This is the SELECT statement for the VEW_DSF_FINAL

MattAllington
Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: SUMX Giving Incorrect Grand Total

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])))
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

smjzahid
Posts: 7
Joined: Sat Sep 08, 2018 10:19 pm

Re: SUMX Giving Incorrect Grand Total

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)

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`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.

MattAllington
Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: SUMX Giving Incorrect Grand Total

Sorry, I can’t tell from what you have pasted what the issue is. Can you just paste the exact formul you are typing?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

smjzahid
Posts: 7
Joined: Sat Sep 08, 2018 10:19 pm

Re: SUMX Giving Incorrect Grand Total

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]))))

MattAllington
Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: SUMX Giving Incorrect Grand Total

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
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

smjzahid
Posts: 7
Joined: Sat Sep 08, 2018 10:19 pm

Re: SUMX Giving Incorrect Grand Total

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.
Attachments
The Table header should reflect the filter selection by the user
Filter Selection.JPG (54.37 KiB) Viewed 1885 times

MattAllington
Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: SUMX Giving Incorrect Grand Total

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])
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training