Forum Discussion
KPI Indicator on table with values in rows
Here's a different approach that allows separate formatting for the metric value and trend arrow.
1. Create a disconnected table (no relationships) with a row for each metric:
2. Create measures:
Display Metric = SWITCH ( SELECTEDVALUE ( MetricTable[Metric] ), "Metric 1", [Metric 1], "Metric 2", [Metric 2], "Metric 3", [Metric 3] )Display Metric Icon = VAR vMetric = [Display Metric] VAR vResult = SWITCH ( TRUE, vMetric >= .1, UNICHAR ( 9650 ), vMetric > .05, UNICHAR ( 9654 ), vMetric <= .05, UNICHAR ( 9660 ) ) RETURN vResult3. Add fields to matrix (MetricTable[Metric] is in Rows field well):
4. Result:
Apply formatting/conditional formatting as required. The measure [Display Metric Icon] can be expanded to use nested SWITCH expressions, allowing you to specify different thresholds for each metric.
Hi JohnSalt ,
In the table visual object, you can apply conditional formatting to the Trend column to change the background color or font color based on the metric. This does not give you arrows, but you can use the color to indicate an upward (green) or downward (red) trend.
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply,
The issue with applying RAG to the values is that I am reporting financial figures where the standard is negative values are in brackets and red, so they dont show on a red formatted cell. I can add trend arrows to a normal matrix no problem its just that I cant when I switch values to rows.
Here's an approach that might be viable. Create two measures like these for each metric (you can use any UNICHAR characters or color codes).
Metric 1 With Icon = VAR vMetric = [Metric 1] VAR vIcon = SWITCH ( TRUE, vMetric >= .1, UNICHAR ( 9650 ), vMetric > .05, UNICHAR ( 9654 ), vMetric <= .05, UNICHAR ( 9660 ) ) RETURN FORMAT ( vMetric, "0%" ) & " " & vIconMetric 1 Formatting = VAR vMetric = [Metric 1] VAR vResult = SWITCH ( TRUE, vMetric >= .1, "#0A8228", vMetric > .05, "#D9B300", vMetric <= .05, "#991A1A" ) RETURN vResultApply conditional formatting to each metric using its corresponding Formatting measure. I renamed [Metric 1 With Icon] to [Metric 1] in the visual.
Result:
Thanks, this is kind of part way there, it puts a trend arrow against each value as opposed to just the last but thats not too much of a problem.
The remaining issue is that the values inherit the trend arrow format which i dont want as finanical negatives have to be in brakets and red, so we can have a negative value that is less than the prior months negative (both in red) but the trend is towards zero so should be a green trend arrow (value = red, trend = green).
Can part of a measure be formatted differently to the rest?
Here's a different approach that allows separate formatting for the metric value and trend arrow.
1. Create a disconnected table (no relationships) with a row for each metric:
2. Create measures:
Display Metric = SWITCH ( SELECTEDVALUE ( MetricTable[Metric] ), "Metric 1", [Metric 1], "Metric 2", [Metric 2], "Metric 3", [Metric 3] )Display Metric Icon = VAR vMetric = [Display Metric] VAR vResult = SWITCH ( TRUE, vMetric >= .1, UNICHAR ( 9650 ), vMetric > .05, UNICHAR ( 9654 ), vMetric <= .05, UNICHAR ( 9660 ) ) RETURN vResult3. Add fields to matrix (MetricTable[Metric] is in Rows field well):
4. Result:
Apply formatting/conditional formatting as required. The measure [Display Metric Icon] can be expanded to use nested SWITCH expressions, allowing you to specify different thresholds for each metric.