Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Filled Map Conditional Formatting for aggregated amounts

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. 

jakerussell406_0-1724260271769.png

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

jakerussell406_1-1724260345326.png

 

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. 

 

5 REPLIES 5
Anonymous
Not applicable

Hi @Anonymous ,

 

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:

vyajiewanmsft_0-1724312010021.png

Best regards,

Joyce

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

Anonymous
Not applicable

@Anonymous 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")

 

Anonymous
Not applicable

Hi @Anonymous ,

 

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.

Anonymous
Not applicable

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"
 )

jakerussell406_1-1725377445939.pngjakerussell406_2-1725377478529.png

 

Any additional thoughts? Thanks!

 

Anonymous
Not applicable

Hi @Anonymous ! 

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? 

jakerussell406_0-1724680845214.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.