Matrix visual with different formats and YoY conditional formatting icons

[sheepworrier]

I'm trying to build a Matrix visual to summarise survey results like this:

I want most rows formatted as %s and one row as a whole number.  I also want to display conditonal formatting (arrow icons) to display significant increases or decreases year on year.

My sample data looks like this:

Year	Metric	Value
01/012015	Number of responses	474
01/01/2015	Q1	0.61
01/01/2015	Q2	0.82
01/01/2015	Q3	0.79
01/01/2015	Q4	0.76
01/01/2016	Number of responses	532
01/01/2016	Q1	0.68
01/01/2016	Q2	0.85
01/01/2016	Q3	0.91
01/01/2016	Q4	0.98

I created a standard CALENDERAUTO() date table; a Dimension table for categorising and ordering the Metrics and then joined them like this:

1st Attempt

My 1st attempt using two Measures (one for the Value; one for the arrow icon) worked fairly well, but resulted in formatting problems:

✅Matrix visual of survey results

✅Arrow icons showing significant (more than 5% YoY) change

❌Bad formatting - should be a whole number for number of responses and a % for the other rows

Score = SUM('Survey data'[Value])

YoY change = 
VAR _PrevYearScore =
CALCULATE(
    [Score],
    SAMEPERIODLASTYEAR('Date'[Date])
)
RETURN IF(OR(ISBLANK(_PrevYearScore), SELECTEDVALUE(Metrics[Metric]) = "Number of responses"), BLANK(), [Score] - _PrevYearScore)

Attempt 2

Generally when I want to have different formats in a Matrix visual I set up a Calculation Group, so I tried that (called Survey Metrics) and improved the formatting, but I couldn't then get the arrow icons to work:

✅Matrix visual of survey results

❌Arrow icons not showing significant (more than 5% YoY) change

✅Better formatting

I was naively trying to create the arrow icons as a 2nd measure on the Calcuation Group, which unexpectedly results in the same value as the other metric, even though their definitions are very different:

Selected Metric = SELECTEDMEASURE()

YoY change (Calculation Group) = 
VAR _PrevYearScore =
CALCULATE(
    SELECTEDMEASURE(),
    SAMEPERIODLASTYEAR('Date'[Date])
)
RETURN IF(OR(ISBLANK(_PrevYearScore), SELECTEDVALUE('Survey Metrics'[Metric]) = "Number of responses"), BLANK(), [Score] - _PrevYearScore)

3rd attempt???

How can I meet all three requirements?  My Google searching suggests using a 2nd Calculation Group, but I really don't follow any of the examples of these I've seen.  Any thoughts on an easier solution?

[v-yifanw-msft]

Hi @sheepworrier ,

Based on the information you provided, you can follow these steps:

1. In order to format the rows differently in the same Matrix visual object, using calculation groups is really a sensible approach. It allows you to dynamically change the format of the metrics based on the context of the selected calculation item.

By improving the formatting through calculation groups, you have already made progress in this area. To refine this further, make sure that the Calculation Group item for the Percentage metric and the Response Count metric define appropriate format strings.
See the official documentation for more details:

Create calculation groups in Power BI (preview) - Power BI | Microsoft Learn

2. For conditional formats with arrow icons to indicate significant year-over-year changes, the conditional format can be applied directly to Matrix visual objects. Since the second metric with the arrow icon did not produce the expected results, let's consider another approach.
Instead of trying to process the arrow icons by calculating groups, you can apply the conditional format directly to the Matrix visual object. This involves setting up rules for the icons based on your calculated year-over-year change metric values.

To apply conditional formatting using icons, do the following:

Select the Matrix visual object.
In the Visual Effects pane, find the metric to which you want to apply the icon.
Click the drop-down arrow next to the metric and select Conditional Format.
Select Icons and set the rule to display different icons based on the YoY value.
For more information, visit:

Apply conditional table formatting in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sheepworrier · Accepted solution]

Thanks @v-yifanw-msft for taking the time to reply and giving some useful links to explore.  In the end, though, I was able to solve this without calculation groups and using a relatively new feature within Power BI:

Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn

Against each Metric I could record the format (and additional information like background colour to highlight a specific row) and then use SELECTEDVALUE in the Measure's Dynamic format (and the Matrix conditional format section)