Forum Discussion
Multiple measures in matrix by year
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.
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.
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?
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.
