Forum Discussion
View in Matrix
How can I achieve below view in Matrix-
Current Month as a whole and underneath that 3 sub columns-totalorders, on-timeorders and % and similar with last 3 months and 6.
I am unable to get how to do the the current month as a whole on top of the 3 coloumns which are underneath it.
Hi learner03 ,
First of all, the matrix table cannot edit the column name itself, so we need to create a table to contain the column name we need, like this one.
In the second step, create a new column in your calendar table that will be used to calculate how many months from today the date of this line of orders is. Via this code : DidffMonth = DATEDIFF([Date],TODAY(),MONTH)
Finally, create three measures, and let the filter context of the measure change with the names of the different columns. Please modify these following measures to suit your model.
Total order = VAR _S = SELECTEDVALUE(COLUMN_NAME[Value2]) RETURN CALCULATE(SUM(FactTable[Values]),'CALENDAR'[DidffMonth]<=_S) On time orders = var _s = SELECTEDVALUE(COLUMN_NAME[Value2]) return CALCULATE(COUNT(FactTable[Values]),'CALENDAR'[DidffMonth]<=_s) % = DIVIDE([On time orders],[Total order] )And the final result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi learner03 ,
First of all, the matrix table cannot edit the column name itself, so we need to create a table to contain the column name we need, like this one.
In the second step, create a new column in your calendar table that will be used to calculate how many months from today the date of this line of orders is. Via this code : DidffMonth = DATEDIFF([Date],TODAY(),MONTH)
Finally, create three measures, and let the filter context of the measure change with the names of the different columns. Please modify these following measures to suit your model.
Total order = VAR _S = SELECTEDVALUE(COLUMN_NAME[Value2]) RETURN CALCULATE(SUM(FactTable[Values]),'CALENDAR'[DidffMonth]<=_S) On time orders = var _s = SELECTEDVALUE(COLUMN_NAME[Value2]) return CALCULATE(COUNT(FactTable[Values]),'CALENDAR'[DidffMonth]<=_s) % = DIVIDE([On time orders],[Total order] )And the final result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.