Forum Discussion

spathak04's avatar
spathak04
Helper II
7 years ago
Solved

How to apply legends in shape Map visual using calculated column or dax

Hello, Scenario is, in my data set I have 20 columns which are attributes like GDP, Population etc. Then I created a dimension table having all these attributes in it and put them in  a slicer. I ...
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi spathak04

    In Queries Editor

    Select “GDP”,”Population”.. columns, then select “Unpivot columns”

     

    Close &&Apply, Go to Data View, create a calculated column

    category =
VAR GDPCategory =
    IF (
        Sheet3[Value] >= 25,
        "25% or more",
        IF (
            Sheet3[Value] >= 10,
            "10% - 25%",
            IF (
                Sheet3[Value] >= 3,
                "3% - 10%",
                IF (
                    Sheet3[Value] > 0,
                    "0% - 3%",
                    IF ( Sheet3[Value] < 0, "less than 0%", "no data" )
                )
            )
        )
    )
VAR PopulationCategory =
    IF (
        Sheet3[Value] >= 3000,
        "3000 or more",
        IF (
            Sheet3[Value] >= 1200,
            "1200 - 3000",
            IF ( Sheet3[Value] >= -550, "-550 - 1200", "no data" )
        )
    )
RETURN
    SWITCH (
        Sheet3[Attribute],
        "GDP", GDPCategory,
        "Population", PopulationCategory
    )

     

     

    Best Regards

    Maggie

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi spathak04

In Queries Editor

Select “GDP”,”Population”.. columns, then select “Unpivot columns”

 

Close &&Apply, Go to Data View, create a calculated column

category =
VAR GDPCategory =
    IF (
        Sheet3[Value] >= 25,
        "25% or more",
        IF (
            Sheet3[Value] >= 10,
            "10% - 25%",
            IF (
                Sheet3[Value] >= 3,
                "3% - 10%",
                IF (
                    Sheet3[Value] > 0,
                    "0% - 3%",
                    IF ( Sheet3[Value] < 0, "less than 0%", "no data" )
                )
            )
        )
    )
VAR PopulationCategory =
    IF (
        Sheet3[Value] >= 3000,
        "3000 or more",
        IF (
            Sheet3[Value] >= 1200,
            "1200 - 3000",
            IF ( Sheet3[Value] >= -550, "-550 - 1200", "no data" )
        )
    )
RETURN
    SWITCH (
        Sheet3[Attribute],
        "GDP", GDPCategory,
        "Population", PopulationCategory
    )

 

 

Best Regards

Maggie

spathak04's avatar
spathak04
Helper II
7 years ago

v-juanli-msft,
It's a great help, I was really looking for this kind of solution. Thank you so much.

Can you please tell me one more thing, how can I sort the legends also.
For example 6-3 should come first, then 3-0, then 0 then no data. 
How can I sort legends?

THANKS IN ADVANCE