DAX - Location query

[SuperCal99]

Hi,

I have the following sample data set:

customerID	County	Region
10001	Greater London	London
10002	Kent	South East
10003	Surrey	South East
10004	Buckinghamshire	South East
10005	West Sussex	South East
10006	Oxfordshire	South East
10007	Hampshire	South East
10008	Isle of Wight	South East
10009	East Sussex	South East
10010	Berkshire	South East
10011	Hertfordshire	East of England
10012	Norfolk	East of England
10013	Suffolk	East of England
10014	Bedfordshire	East of England

I essentially want to create a table like the below, based on customer location:

County	% across all counties	%  within region
Greater London	20%	92%
Kent	16%	36%
Surrey	14%	56%

Across all counties is fine, but the % within region I have stuggling with. I get the correct values if i use region as a filter, for example, within the London region is 2 counties, Greater London and City London, which returns 99.8% and 0.2% respectively. I just want to achieve these figure without the use of a filter.

Thanks in advance for any guidance and help

Cal

[some_bih · Accepted solution]

Hi @SuperCal99 based on your sample data, create 3 measures as following, adjust your Table name for sheet1

Did I answer correctly? Kudos appreciate / accept solution.

#Dist cust = DISTINCTCOUNT(Sheet1[customerID])

% across all counties =

DIVIDE([#Dist cust],CALCULATE([#Dist cust],ALL()))

%  within region =

DIVIDE([#Dist cust],

    CALCULATE([#Dist cust],

    REMOVEFILTERS(Sheet1),

    VALUES(Sheet1[Region])

    )

)

[SuperCal99]

Hi @some_bih , many thanks for your response
I have created the measures you provided, the "%within region" measure generates the following error when adding it to the visual:

MdxScript(Model) (62.9) Calculation error in measure 'ballot' [withinregion]: The alternative result to return on divide by zero cases must be a constant numeric value

[some_bih]

Hi @SuperCal99 measure reference should be only with [xxx] not table before it, put [withinregion].

Still, measure definition is based on another measure [#Dist cust], so process is important here, first create this measure [#Dist cust], and after that [withinregion].