SUMX SUMMARIZE group by company in a table

MarcoT — Fri, 15 Mar 2024 15:03:42 GMT

Hi all. I have a simple table made of 2 fields and 1 measure:

where

_Sales Denominator YA ORG =

CALCULATE(
[_Sales YA ORG]
, ALL(Company[Company], Company[Brand],Company[Sub Brand], Company[Variant])
, ALLSELECTED(Period[Period])
,ALLSELECTED('Market Breakdown'[Country])
,ALLSELECTED('Organic Claim'[Organic Claim])
, VALUES('W Segment'[W Segment])
, VALUES('Market Brkdown'[Hier Level Name])
, VALUES('Ad Hoc Analysis View'[Category])
, VALUES('Ad Hoc Analysis View'[Segment])
)

what I want to do is to delete the country field and obtain one row for each company with the sum of each _Sales denominator YA ORG for each company. For example I want this result:

Company	_Sales denominator YA ORG
PRIVATE LABEL	3402952
STRAUSS GROUP	206
TCHIBO	36854
TONYS COFFEE ROASTERS	2895
VERGNANO	1131
WERTFORM	3987
WICKED JOE LLC	23592
WOSEBA	1035

So what I've tried is to modify the measure this way:

_Sales denominator YA ORG=

SUMX(

SUMMARIZE( 'Ad Hoc Analysis View',

'Ad Hoc Analysis View'[Company],

"x_per_company",

CALCULATE( [_Sales YA ORG],

ALL(Company[Company], Company[Brand],Company[Sub Brand], Company[Variant]),

ALLSELECTED(Period[Period]),

ALLSELECTED('Market Brkdown'[Country]),

ALLSELECTED('Organic Claim'[Organic Claim]),

VALUES('W Segment'[W Segment]),

VALUES('Market Brkdown'[Hier Level Name]),

VALUES('Ad Hoc Analysis View'[Category]),

VALUES('Ad Hoc Analysis View'[Segment])

)

), [x_per_company]

)

But I obtain this table, with summarized values that are different from what I expect:

Do you have any suggestion? Thanks

Re: SUMX SUMMARIZE group by company in a table

lbendlin — Fri, 15 Mar 2024 17:44:22 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

topic Re: SUMX SUMMARIZE group by company in a table in DAX Commands and Tips

SUMX SUMMARIZE group by company in a table

Re: SUMX SUMMARIZE group by company in a table