Need help in Matrix visual

[sathyaramesh]

Hi,

I need to achieve the same structure in matrix visual as below.

I can able to achieve month columns but when i place  (average ,xyz,abc) measure or column it started repeating in all the month column to.

i need  (average ,xyz,abc) should not repeat in each month column. should be in seperate column. @v-lid-msft

Thanks in Advance.

[AnthonyTilley · Accepted solution]

this is a tricky one but i have something for it see below

i achived this using a headers table.

HeaderOrderTypeDetails

YEARS	1	1	1
AVARAGE	2	2	0
XYZ	3	3	0
ABC	4	4	0

the 4 colunms are important, the first is what you want to appear at the top of your table in the header

the second allows you to change the order of the headers in the table so that the Avarage, XYZ and ABC appear at the end, without this they would be ordered alphabeticaly.

the third is used to do a switch between diffrent types of measures for example in the first you wan tthe sum of a coulnm, in the the second header you want the avarge and in the thired you want the max. this will be explained later.

the thired is used to make sure that the months and years only appear in the first header and not in the other 3 

Step 1 - insert the table above using the enter table bitton

step 2 - Create a colunm in your date called header 

HEADER = "YEARS"

step 3 - Join your date to the headers table on the new header colunm 

Step 4 - create a colunm for Year, Month and month value

Year = year('Table'[date])

m_value = month([date])

month = FORMAT('Table'[date].[Date],"MMMM")

these three colunms will be used later

step 5 - create two meaasures for max header type and max header details

MAX_TYPE = MAX(HEADERS[Type])

H_Details = max(HEADERS[Details])

Step 6 create the final measure

Measure = 
var type_1 = Sum('Table'[AMT])
var type_2 = CALCULATE(AVERAGE('Table'[AMT]),ALL('Table'))
var type_3 = "XYZ"
var type_4 = "ABC"

var check_type = SWITCH([MAX_TYPE]
                    ,1,type_1
                    ,2,type_2
                    ,3,type_3
                    ,4,type_4)
var ret = if(and([H_Details] = 0,ISFILTERED('Table'[month])) , BLANK(),check_type)
return ret

the measure above is split in to 3 parts 

the top there are 4 veriables this holds the value you want to display in each of your headers. you can make 4 distinct measures and refrence these or do as i have and put the calculation in the veriables directly 

the second part is a switch statment that will swap between the top veriables based on the max header type.

the final step will then check to see if the the header should show sub details, if details in the header table is set to 0 then this will make sure that only the total colunm is shown in the table and the indivisual months and years are set to blank 

hope this helps 

my sample file below 

Sample file

[sathyaramesh]

Thank you so much for the replay. can get the sample file? I sent you a request @AnthonyTilley 

[AnthonyTilley]

apologies i have made the link public now