Requirement: We need to create a matrix visual which contains country on rows, Budget & Month on columns. As Year is a mandatory filter, matrix always shows data for one year.Refer the below structure to get more context about it.
Need help of calculating Avg and YTD columns, these should be part of Month column. Could you please provide possible solutions for this.
Need help of calculating Avg and YTD columns, these should be part of Month column.
I don't think there is an easy way to do this with the current Matrix visual.
I have tried to create a new table which contains the month name value, "Avg" and "YTD" like below. And create a relationship between the new table and the Date table with MonthName.
However, I was not able to figure out how to calculate Avg, YTD for each country, as there is no related country value for "Avg" and "YTD" in each Matrix row.
Measure = SWITCH ( FIRSTNONBLANK ( MonthTable[Month], 1 ), "Avg", [Avg] + 0, "YTD", "YTD", SUM ( Sales[Revenue] ) )
Just hope it can give you some hint.