Forum Discussion
Filled Map Conditional Formatting for aggregated amounts
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.
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 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")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.
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!