Subtotal issue using summarize and sumx

[souvik900766]

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.

[wdx223_Daniel]

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

[souvik900766]

Hi @wdx223_Daniel ,

Unfortunately, its not working.

[wdx223_Daniel]

[souvik900766]

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

[VahidDM]

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/

[souvik900766]

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

[smpa01]

@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/

[souvik900766]

@smpa01 any way, what to use instead

[smpa01]

@souvik900766  provide sample data, expected output

[souvik900766]

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