Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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. 

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. 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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:

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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? 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.