Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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.
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
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.
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?
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.
Hi,
Thanks for update and confirming.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |