Creating percentile tiers that can be mapped in Power BI

[lisago1978]

i need some help, I was able to create new measures that correctly gave me percentiles of my summary data, but I don't know how I can then map this as "tiers" on a shape map since it is not a column rather it is a "measure"

1. First, I grouped(summarized) my data using Power Query. Here is the M code

let
Source = Value.NativeQuery(PostgreSQL.Database("data-is.acphd.us:45432", "cape", [CreateNavigationProperties=false]), "SELECT * FROM inquiries_log_view_v2", null, [EnableFolding=true]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date/Time of Call", type datetime}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Entry ID", "Inquirer City of Residence"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Inquirer City of Residence"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"

Here is the table

City of Residence	Count
Alameda	380
Albany	54
Ashland	1
Berkeley	163
Castro Valley	119
Cherryland	1
Did Not Provide	3224
Dublin	203
Emeryville	57
Fairview	1
Fremont	535
Hayward	515
Livermore	391

2. Then, I started creating my new measures for each tertile (.75, .5, and .5)

_25_Percentile = CALCULATE(PERCENTILEX.INC('Location', [Count],0.25), ALL('Location'[Inquirer City of Residence]))
_50_Percentile = CALCULATE(PERCENTILEX.INC('Location', [Count],0.50), ALL('Location'[Inquirer City of Residence]))
_75_Percentile = CALCULATE(PERCENTILEX.INC('Location', [Count],0.75), ALL('Location'[Inquirer City of Residence]))

3. Next, I created my tiers based on these percentiles using if statements

Tier = IF([_Count]<=[_25_Percentile],"Low",(IF(AND([_Count]<=[_50_Percentile],[_Count]>[_25_Percentile]),"Medium","Hgh")))

When I use the matrix visual feature all of these measures work, but I cannot figure out how to transition to a map based on city/place where I can shade based on tier since these are dynamic measures.

Here is my matrix Power BI visual. 

City of Residence	Count	_25_Percentile	_50 Percentile	_75 Percentile	Tier
Alameda	380	28	123.5	382.75	Hgh
Albany	54	28	123.5	382.75	Medium
Ashland	1	28	123.5	382.75	Low
Berkeley	163	28	123.5	382.75	Hgh
Castro Valley	119	28	123.5	382.75	Medium
Cherryland	1	28	123.5	382.75	Low
Did Not Provide	3224	28	123.5	382.75	Hgh
Dublin	203	28	123.5	382.75	Hgh
Emeryville	57	28	123.5	382.75	Medium
Fairview	1	28	123.5	382.75	Low
Fremont	535	28	123.5	382.75	Hgh
Hayward	515	28	123.5	382.75	Hgh
Livermore	391	28	123.5	382.75	Hgh

One assumption I have is that a table needs to be created that can be viewed in table views that shows low, medium and high in rows on a summary table, but since these are dynamic, I don't know how to do that.

[v-lionel-msft]

Hi @lisago1978 ,

Like this?

Your address([City of Residence] column) is not detailed enough to draw a map, try to add country and state.

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.

[lisago1978]

Yes, I just changed the category type to City andit worked. Thank you for responding!

[edhans]

@lisago1978 - was the measure I provided able to get you up and running? Do you have the final solution? If so, could you mark the post(s) that solved this for you as the solution so it can be shown to have an answer? If not, can you post back with what you lack in getting this working?

[lisago1978]

Thanks, I did try this, but it was still showing all tiers as highest or 4 in my matrix. The formula seems to work though not sure why it isn't showing it. I decided to go with some pre-set tiers based on categories so that I could complete this.

[lisago1978]

Sorry, I did the formula wrong, I added this formula into the table I have in Power BI as a calculated column. It computed Highest for every row, for some reason.

Test2 =

IF('Location'[Count]<[_25_Percentile],1,

IF(AND('Location'[Count]<[_50_Percentile],'Location'[Count]>=[_25_Percentile]),2,

IF(AND('Location'[Count]<[_75_Percentile], 'Location'[Count]>=[_50_Percentile]),3,

IF('Location'[Count]>=[_75_Percentile],4))))

[edhans]

Hi @lisago1978 - is this what you need?

If so, this is the measure.

Test2 = 
VAR varCurrentLocationCount =
    MAX( Location[Count] )
RETURN
    SWITCH(
        TRUE(),
        varCurrentLocationCount < [_25_Percentile], 1,
        varCurrentLocationCount < [_50_Percentile]
            && varCurrentLocationCount >= [_25_Percentile], 2,
        varCurrentLocationCount < [_75_Percentile]
            && varCurrentLocationCount >= [_50_Percentile], 3,
        4
    )

Also, I don't know your data, so this suggestion may be wrong, but I suggest this instead:

Test2 = 
VAR varCurrentLocationCount =
    MAX( Location[Count] )
RETURN
    SWITCH(
        TRUE(),
        varCurrentLocationCount <= [_25_Percentile], 1,
        varCurrentLocationCount <= [_50_Percentile]
            && varCurrentLocationCount > [_25_Percentile], 2,
        varCurrentLocationCount <= [_75_Percentile]
            && varCurrentLocationCount > [_50_Percentile], 3,
        4
    )

If it is equal to the 25th percentile, the 2nd code sample will put it in the 25th percentile, or 1. Your original logic would have bumped it to the 2nd percentile, or 2. That might be right, it just looked odd to me and thought I would bring it up. You can see that in the Albany result. It is 54, the 1st percentile, but the logic put it in the 2nd. My 2nd measure above keeps it in the 1st group.

Note that this is a measure, and should work for you. In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables

If this still isn't the answer you are looking for, ping back with more info and expected results.

[lisago1978]

Thank you I will try this. THe only reason I went with calculated columns in DAX was so that I could map this in tiers on the Power BI shape map. In general, any calculations I am doing, I try to do using the measures in DAX.

[edhans]

Understood. You can use measures in that filed in the Shape Map, so hopefully this works. Ping back with questions, or mark it as solved.