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 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?

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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      JohnSalt
      Helper I
      2 years ago

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

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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