Forum Discussion
DAX - Location query
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
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])))
3 Replies
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])))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 valueHi 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].
