cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
po
Post Prodigy
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
v-lionel-msft
Community Support
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"
)

ab5.PNG

 

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.

View solution in original post

8 REPLIES 8
v-lionel-msft
Community Support
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.

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?
 
screenshot3.JPG
 
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
Community Support
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.

 

Hi,

 

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

 

NAMEPOSTCODE SECTOR
JOHNEH11 1
JIMEH11 1
JAMESEH11 1
BILLEH11 1
TOMEH11 1
HARRYEH11 1
JACKEH12 1
RICHEH12 2
JENNYEH14 1
AMYEH14 1
SOPHIEEH14 1

 

Thanks

v-lionel-msft
Community Support
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"
)

ab5.PNG

 

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.

View solution in original post

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?

 

legend_sort.JPG

v-lionel-msft
Community Support
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.

a9.PNG    a11.PNG 

 

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.

Hi,

 

Thanks for update and confirming.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!