Filled map - legend based on no of customers within range

[po]

Hi,

Have a filled map based on postcode sectors.

Wish to have legend as Number of customers within that postcode sector

e.g over 20 then show                "20+"

   between 10 and 20 "10 to 20"

   between 0 and 10    "0 to 10"

Tried creating measure as 

No of Customers = DISTINCTCOUNT('All Postcodes'[Customer Name])

then a calculated column

legend =

IF (

[No of Customers] >= 20,"Over 20","Under 20"

)

 as first step.

Not working though anyone have simple example of filled map with legend based on number of customers in that postcode or postcode sector?

Thanks

[v-lionel-msft]

Hi @po ,

You can try this calculated column formula.

Column = 
VAR x = DISTINCTCOUNT( 'All Postcodes'[Customer Name])
RETURN
SWITCH(
    TRUE(),
    x >=0 && x <= 10, "0 to 10",
    x >10 && x <= 20, "10 to 20",
    x > 20, "over 20"
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[po]

Hi,

Thanks fro suggestion.

Tried this adding line but for some reason not working always showing as over 20

vLegend =

VAR x = DISTINCTCOUNT( 'Postcode Matches'[Customer Name])

RETURN

SWITCH(

TRUE(),

x >=0 && x <= 5, "0 to 5",

x >6 && x <= 10, "6 to 10",

x >10 && x <= 20, "10 to 20",

x > 20, "over 20"

)

Also tried as table and still legend variable always shoiwng as over 20.

Simple report with just one table - any reason this woudl be case?

Formula works as a measure but not working for me as a calculated column  which believe need use column rather than measure for legend as doesn't allow me to drag measure into legned column.

Thanks

[v-lionel-msft]

Hi @po ,

This error occurs because the return value of x ( VAR x = DISTINCTCOUNT( 'Postcode Matches'[Customer Name]) ) is fixed.

You try this formula again.

vLegend =
VAR x = [Count of Customer Name]
RETURN
SWITCH(
TRUE(),
x >=0 && x <= 5, "0 to 5",
x >6 && x <= 10, "6 to 10",
x >10 && x <= 20, "10 to 20",
x > 20, "over 20"
)

If the formula still doesn't work, please give me a sample data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[po]

Hi,

Thanks for reply - still not working for me somple sample data below.

NAME	POSTCODE SECTOR
JOHN	EH11 1
JIM	EH11 1
JAMES	EH11 1
BILL	EH11 1
TOM	EH11 1
HARRY	EH11 1
JACK	EH12 1
RICH	EH12 2
JENNY	EH14 1
AMY	EH14 1
SOPHIE	EH14 1

Thanks

[v-lionel-msft · Accepted solution]

Hi @po ,

Try this calculated column formula:

Column = 
VAR x = 
CALCULATE(
    DISTINCTCOUNT(Sheet1[NAME]),
    ALLEXCEPT(
        Sheet1,
        Sheet1[POSTCODE SECTOR]
    )
)
RETURN
SWITCH(
TRUE(),
x >=0 && x <= 1, "0 to 1",
x >1 && x <= 3, "1 to 3",
x >3 && x <= 5, "3 to 5",
x > 5, "over 5"
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[po]

Hi,

Thanks for solution - working great.

Just one questuon though if we wish to adapt and extend e.g 5 to 9,  10 to 15, 15 to 20 21 to 25 etc. notice it shows the legend sorted as string.

Is it possible to have legend sort such that in example below would show 5 to 9 as second entry in the legend?

[v-lionel-msft]

Hi @po ,

It's impossible to achieve it in the current version. In fact, no matter what type of data is always automatically sorted in ascending order.

I used the data from these two tables for testing and found that the returned results were the same.

     

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[po]

Hi,

Thanks for update and confirming.