Multiple measures in matrix by year

Hi all,

I am trying to create a matrix that mirrors the screenshot excel example below, where the 4 measures (actual, contract, variance & % attainment) are shown individually by year with a space between each measure section. I also want to be able to conditionally format the background colour of % attainment section of the matrix.

I created the measure below for values and used the metric column as columns in my visual, with year underneath, however this doesn’t allow for spaces between each section or for conditional formatting.

Historical contract performance =

SWITCH(

SELECTEDVALUE('Historical contract'[Metric]),

"Actual", [Delivered Volume],

"Contract", [Contract Volume],

"Abs Var", [Contract variance],

"% Attainment", [Contract % delivered],

BLANK()

)

Does anyone have any suggestions?

Need Help

danextian
4 months ago
Hi ryan12345 You’ll need to set up a disconnected table that includes all combinations of years and measure names, along with additional rows to represent blank columns. You can then use the columns from this table in your model (without creating relationships to fact tables). For the blank columns, return a zero value, but apply a dynamic format string so they are displayed as spaces instead of zeros. Write another measure for conditional formatting.

Please see the attached pbix.

blank columns in between measures.pbix624 KB

8 Replies

danextian
Super User
4 months ago
Hi ryan12345 You’ll need to set up a disconnected table that includes all combinations of years and measure names, along with additional rows to represent blank columns. You can then use the columns from this table in your model (without creating relationships to fact tables). For the blank columns, return a zero value, but apply a dynamic format string so they are displayed as spaces instead of zeros. Write another measure for conditional formatting.

Please see the attached pbix.

blank columns in between measures.pbix624 KB
- ryan12345
  Frequent Visitor
  4 months ago
  danextian this is ideal thank you! One last nuance I have is that I would like to sort my matrix by contract volume to get the largest customers appearing at the top (ideally using the latest period shown on the visual). Is this a possibility as well?
  - danextian
    Super User
    4 months ago
    Following MFelix's solution, you can return the volume value at the metric column total level. In the image below I returned the Mar (the latest month in the current context) transaction values as the column total.
MFelix
Super User
4 months ago
Hi ryan12345 ,

You can do it just by turning on the subtotals and then making them blank and substituting the Total text by a space:

Before:

After:

No need for additional workarounds, if you don't want this then you need to have additional rows on your Historical Contract Metric table with blank values. And some additional code.

Please let me know if this is good for your needs or you want something programatically.
- ryan12345
  Frequent Visitor
  4 months ago
  thanks MFelix , this getsme close, however I would also like to change the format of the variance section to red if negative and conditional colour the background of the % attainment section, is this something that can be done in this visual?
  - MFelix
    Super User
    4 months ago
    Hi ryan12345 ,
    
    Yes you can use the same measure to do the formatting something similar to this one:
    
    Historical contract performance = SWITCH( SELECTEDVALUE('Historical contract'[Metric]) = "% Attainment" && [Contract % delivered] < 0.1, "Red", SELECTEDVALUE('Historical contract'[Metric]) = "% Attainment" && [Contract % delivered] < 0.5, "Yellow", SELECTEDVALUE('Historical contract'[Metric]) = "% Attainment" && [Contract % delivered] >= 0.5, "Green", BLANK() )
    
    Now use this on the Cell elements condittional background and select the option Field Value.
    
    You can also use the #000000 format for your colors.
Lodha_Jaydeep
Solution Sage
4 months ago
Hi ryan12345,

Somehow can you provide the sample report where you have created this measure and other stuff. If the report has any non-disclousable info you can delete the page. It will be help to assist in better way. Then will give you the updated .PBIX with the chnages you need.

Hope this sounds good!