Filled Map Conditional Formatting for aggregated amounts

[jakerussell406]

Hi, 

I am having issues with conditional formatting within the Azure map visual for filled color. Essentially, the table I have records shipments and whether they were on-time, early, delayed. This data is by day, by state. I have set-up a conditional column that records the appropriate hex code for the assignments above. However, the conditional formatting option by field only allows for a FIRST or LAST situation. This leads to a misleading map at a state level as some states are showing Red but really should be green because he majority of their shipments were early. 

Example: WV is showing red in an aggregated view but only 1 out of 20 shipments were late

Is there a way that I can format by state on an aggregated basis? Gradient color fomatting doesnt give me the result that I want,

My other idea was to create a consolidate table with just state and calcs by state but not sure how that would work with a day time slicer on the page. 

Any suggestions would be greatly appreciated. 

[v-yajiewan-msft]

Hi @jakerussell406 ,

We suggest you create a measure to show the most shipment status per state:

Most Frequent Shipment Status = 
VAR EarlyCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "Early", ALLEXCEPT('Table', 'Table'[State]))
VAR LateCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "Late", ALLEXCEPT('Table', 'Table'[State]))
VAR OnTimeCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "On-time", ALLEXCEPT('Table', 'Table'[State]))
RETURN
SWITCH(
   TRUE(),
   EarlyCount > LateCount && EarlyCount > OnTimeCount, "Early",
   LateCount > EarlyCount && LateCount > OnTimeCount, "Late",
   OnTimeCount > EarlyCount && OnTimeCount > LateCount, "On-time",
   "Early"
)

 Then use the measure to format the color as shown below:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jakerussell406]

@v-yajiewan-msft I tried this but it still isnt filtering properly... master_sku is linked to a table w/ brand category and the relationship is set to both

MostFrequestShipmentStatus = 
 VAR earlycount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "Early", ALLEXCEPT(fbm_delayed_shipements,fbm_delayed_shipements[status],fbm_delayed_shipements[master_sku],fbm_delayed_shipements[location_id]))
 VAR Latecount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "Late", ALLEXCEPT(fbm_delayed_shipements,fbm_delayed_shipements[status],fbm_delayed_shipements[master_sku],fbm_delayed_shipements[location_id]))
 VAR ontimecount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "On time", ALLEXCEPT(fbm_delayed_shipements,fbm_delayed_shipements[status],fbm_delayed_shipements[master_sku],fbm_delayed_shipements[location_id]))
 RETURN
 SWITCH(
    TRUE(),
    earlycount > Latecount && earlycount > ontimecount, "Early",
    Latecount > earlycount && Latecount > ontimecount, "Late",
    ontimecount > earlycount && ontimecount > Latecount, "On-time",
    "Early")

[v-yajiewan-msft]

Hi @jakerussell406 ，

ALLEXCEPT will ignore dimensional table slicer, remove it to check the result.

Most Frequent Shipment Status = 
VAR EarlyCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "Early")
VAR LateCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "Late")
VAR OnTimeCount = CALCULATE(COUNTROWS('Table'), 'Table'[Shipment Status] = "On-time")
RETURN
SWITCH(
   TRUE(),
   EarlyCount > LateCount && EarlyCount > OnTimeCount, "Early",
   LateCount > EarlyCount && LateCount > OnTimeCount, "Late",
   OnTimeCount > EarlyCount && OnTimeCount > LateCount, "On-time",
   "Early"
)

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jakerussell406]

Hi Joyce!

Thanks for the response. I removed the allexcept formula and it doesnt seem to be working as expected, still. 

Most_Freq_Ship_Status = 
 VAR earlycount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "Early")
 VAR latecount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "Late")
 VAR ontimecount = CALCULATE(COUNTROWS(fbm_delayed_shipements),fbm_delayed_shipements[status] = "On Time")
 RETURN
 SWITCH(
    TRUE(),
    earlycount>latecount && earlycount > ontimecount, "Early",
    latecount > earlycount && latecount > ontimecount, "Late",
    ontimecount > earlycount && ontimecount > latecount, "On-time",
    "Early"
 )

Any additional thoughts? Thanks!

[jakerussell406]

Hi @v-yajiewan-msft ! 

Thanks for responding! This works for the highest level of data. However, when I filter by a dimensional table tied to this table, the visual is showing green for a state that only has a late order. Do I need to add something to the measure that accounts for each slicer on the page?