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.

DataInsights's avatar
DataInsights
Super User
2 years ago

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