Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JohnSalt
Helper I
Helper I

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?

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

 

DataInsights_0-1715086247624.png

 

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

 

DataInsights_1-1715086346959.png

 

4. Result:

 

DataInsights_2-1715086437035.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
v-kongfanf-msft
Community Support
Community Support

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
Community Support
Community Support

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.

vkongfanfmsft_0-1714617461410.png

vkongfanfmsft_1-1714617524676.png

 

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.

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

 

DataInsights_0-1714761721618.png

 

Result:

 

DataInsights_1-1714761792695.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

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

 

DataInsights_0-1715086247624.png

 

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

 

DataInsights_1-1715086346959.png

 

4. Result:

 

DataInsights_2-1715086437035.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.