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’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.
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?
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.
Hi danextian , I am struggling to get the sort order to work, see current measure below which works fine but I want to sort the matrix on highest to lowest contract in last year in the matrix
MeasuresTable Value1 =CALCULATE (SWITCH (SELECTEDVALUE ( 'MeasuresTable 2'[Sort] ),1, [Delivered volume],2, 0,3, [Contract volume],4, 0,5, [Contract variance],6, 0,7, [Contract % delivered 2]),TREATAS ( VALUES ( 'MeasuresTable 2'[Year] ), DateTable[Year] ))
