Forum Discussion

SuperCal99's avatar
SuperCal99
Helper I
3 years ago
Solved

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 ...
  • some_bih's avatar
    some_bih
    3 years ago

    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])
        )
    )
     

     

some_bih's avatar
some_bih
Community Champion
3 years ago

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's avatar
SuperCal99
Helper I
2 years ago

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's avatar
    some_bih
    Community Champion
    2 years ago

    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].