topic Re: Subtotal issue using summarize and sumx in DAX Commands and Tips

Subtotal issue using summarize and sumx

souvik900766 — Wed, 22 Dec 2021 08:34:55 GMT

Hi,
My measure using sumx and summarize showing values at market level 0.
I wanted to add all the market numbers to get the OU total ,the markets are coming zero and OUs also
My OU and Market come from same table.

My expected result is :- Correct total at OU level in Measure " Ind Mix Imact on shares PY ". I tries with the correct total one but it showing wrong numbers and ZEROS in market level.

Here is the link to the file :- https://www.dropbox.com/s/7oq1viv0f7of033/Total%20error.pbix?dl=0

Any help will be appreciated.
Thanks in advance.

Re: Subtotal issue using summarize and sumx

smpa01 — Tue, 21 Dec 2021 15:06:45 GMT

souvik900766 the rule of thumb is not to use any aggregation inside SUMMARIZE as it is really complex.

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

Re: Subtotal issue using summarize and sumx

souvik900766 — Tue, 21 Dec 2021 15:16:24 GMT

smpa01 any way, what to use instead

Re: Subtotal issue using summarize and sumx

smpa01 — Tue, 21 Dec 2021 15:19:21 GMT

souvik900766 provide sample data, expected output

Re: Subtotal issue using summarize and sumx

VahidDM — Tue, 21 Dec 2021 22:43:51 GMT

Hi souvik900766

SUMX(<table>, <expression>)

Change the expression part of your formula to [_1] then check the result.

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

Re: Subtotal issue using summarize and sumx

souvik900766 — Wed, 22 Dec 2021 07:04:09 GMT

Hi VahidDM ,
Here is the file,
Kindly help, urgently needed.
https://www.dropbox.com/s/7oq1viv0f7of033/Total%20error.pbix?dl=0

Re: Subtotal issue using summarize and sumx

souvik900766 — Wed, 22 Dec 2021 07:05:05 GMT

Hi smpa01
Here is the file,
Kindly help, urgently needed.
https://www.dropbox.com/s/7oq1viv0f7of033/Total%20error.pbix?dl=0

Re: Subtotal issue using summarize and sumx

wdx223_Daniel — Wed, 22 Dec 2021 07:50:34 GMT

Ind Mix Imact on shares PY = SUMX(VALUES(GTC[Market]),[Industry Mix Change Vs PY]*[Share within Ind Market PY])

Re: Subtotal issue using summarize and sumx

souvik900766 — Wed, 22 Dec 2021 08:05:58 GMT

Hi wdx223_Daniel ,

Unfortunately, its not working.

Re: Subtotal issue using summarize and sumx

wdx223_Daniel — Wed, 22 Dec 2021 08:44:06 GMT

Re: Subtotal issue using summarize and sumx

souvik900766 — Wed, 22 Dec 2021 09:09:48 GMT

Hi wdx223_Daniel
All the numebers at market level is coming 00 !