KPI Indicator on table with values in rows

[JohnSalt]

Hi

I have a visual with the "Switch values to rows" option selected so I have months across the top and each row is a different measure, this works fine but I cant find how to add a KPI direction arrow / colour, i want this to be in a column at the end marked 'Trend'? I have measures for the trend but I just cant get them to sit in an end column.

Any ideas?

[v-kongfanf-msft]

Hi @JohnSalt ,

Did @DataInsights   reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[v-kongfanf-msft]

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.

[JohnSalt]

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.

[DataInsights]

@JohnSalt,

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%" ) & " " & vIcon

Metric 1 Formatting = 
VAR vMetric = [Metric 1]
VAR vResult =
    SWITCH (
        TRUE,
        vMetric >= .1, "#0A8228",
        vMetric > .05, "#D9B300",
        vMetric <= .05, "#991A1A"
    )
RETURN
    vResult

Apply conditional formatting to each metric using its corresponding Formatting measure. I renamed [Metric 1 With Icon] to [Metric 1] in the visual.

Result:

[JohnSalt]

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?

[DataInsights · Accepted solution]

@JohnSalt,

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
    vResult

3. 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.