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
sheepworrier
Frequent Visitor

Matrix visual with different formats and YoY conditional formatting icons

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

 

sheepworrier_0-1713281574575.png

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:

 
YearMetricValue
01/012015Number of responses474
01/01/2015Q10.61
01/01/2015Q20.82
01/01/2015Q30.79
01/01/2015Q40.76
01/01/2016Number of responses532
01/01/2016Q10.68
01/01/2016Q20.85
01/01/2016Q30.91
01/01/2016Q40.98

 

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

 

sheepworrier_0-1713283190338.png

 

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

sheepworrier_1-1713281780528.png

 

 

 

 

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

sheepworrier_2-1713281879735.png

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:

sheepworrier_3-1713282016208.png

 

 

 

 

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?

1 ACCEPTED SOLUTION

Thanks @Anonymous 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)

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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.

 

Thanks @Anonymous 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)

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors