Forum Discussion
Create a Matrix to Compare Multiple Metrics under Two Time Periods
Eason,
Thank you for your help. With a few edits I got what I needed! I was getting some errors using the relationship of dates we set up earlier, so I changed both date tables to relate directly to my metric table. Then I had to change how I defined Date1 to accomadate this. Lastly, because of some formatting needs I changed the Gain loss column to also use the SWITCH function. I cannot load my database onto the community forum (though thank you so much for doing that it was very helpful), so I will include as many screenshots as I can below. Thank you!!
Hope this helps people in the future!
Callan
Thanks for your ideas! I think this is close but I need something a little different. Each of the rows is a seperate measure and the columns are time frames. Then thier intersection is that measure in that time frame. Take a look at the excel mock up for a better explination. I have seen multiple solutions where the time frames become values, but if thats true I don't know how to get multiple measures to be my rows. Does this make sense?
Hi, cgarst
To get multiple measures to be rows, you need to enter a new table containing all your measure names first.
Then add new measures as below to replace my original meause.
Measure(Date1) =
SWITCH (
SELECTEDVALUE ( 'Table'[Measure row name] ),
"Measure1_Total", MeasureTable[Total_Sales(Date1)],
"Measure2_Average", MeasureTable[Average_Sales(Date1)],
"Measure3_Max", MeasureTable[Max_Sales(Date1)]
)
Measure(Date2) =
SWITCH (
SELECTEDVALUE ( 'Table'[Measure row name] ),
"Measure1_Total", MeasureTable[Total_Sales(Date2)],
"Measure2_Average", MeasureTable[Average_Sales(Date2)],
"Measure3_Max", MeasureTable[Max_Sales(Date2)]
)
Gain/loss = MeasureTable[Measure(Date1)]-MeasureTable[Measure(Date2)]
Best Regards,
Community Support Team _ Eason
Eason,
Thank you for your help. With a few edits I got what I needed! I was getting some errors using the relationship of dates we set up earlier, so I changed both date tables to relate directly to my metric table. Then I had to change how I defined Date1 to accomadate this. Lastly, because of some formatting needs I changed the Gain loss column to also use the SWITCH function. I cannot load my database onto the community forum (though thank you so much for doing that it was very helpful), so I will include as many screenshots as I can below. Thank you!!
Hope this helps people in the future!
Callan