Forum Discussion
matrix with measure
Hi,
I have a test file here https://1drv.ms/u/s!ApIDnMK2eKiFgR2WPdMwZSRWVJhH?e=MgCVqO
how can i create a matrix as below ? Not sure how to move the measure to the column
thanks
Hi ktt777
You need to change your table structure, your sheet2 change to like this, unpivot and add a column (done in Power Query Editor), then it can connect with your sheet1
Use DAX to add a custom table
The matrix, Process from sheet1, Period from the table above, test measure as below
test = VAR CurValue = SELECTEDVALUE('Table'[Period]) RETURN SWITCH(CurValue, "Base", SUM(Sheet1[Base]), "Target",SUM(Sheet1[Target]), AVERAGEX(FILTER(Table1,Table1[Period]=CurValue),Table1[Value]))
1 Reply
Hi ktt777
You need to change your table structure, your sheet2 change to like this, unpivot and add a column (done in Power Query Editor), then it can connect with your sheet1
Use DAX to add a custom table
The matrix, Process from sheet1, Period from the table above, test measure as below
test = VAR CurValue = SELECTEDVALUE('Table'[Period]) RETURN SWITCH(CurValue, "Base", SUM(Sheet1[Base]), "Target",SUM(Sheet1[Target]), AVERAGEX(FILTER(Table1,Table1[Period]=CurValue),Table1[Value]))