Forum Discussion

JohnSalt's avatar
JohnSalt
Helper I
2 years ago
Solved

KPI Indicator on table with values in rows

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...
Need Help
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    2 years ago

    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.

JohnSalt's avatar
JohnSalt
Helper I
2 years ago

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's avatar
DataInsights
Super User
2 years ago

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.