cancel
Showing results for
Did you mean:  Post Prodigy

## Filled map - legend based on no of customers within range

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
1 ACCEPTED SOLUTION  Community Support

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.

8 REPLIES 8  Community Support

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.  Post Prodigy

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  Community Support

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.  Post Prodigy

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  Community Support

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.  Post Prodigy

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?   Community Support

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.  Post Prodigy

Hi,

Thanks for update and confirming.  