Create a Matrix to Compare Multiple Metrics under Two Time Periods

[cgarst]

Hello all!

I am realitively new to PowerBI and have been asked to recreate an excel sheet in PowerBi, and I am having trouble with the formatting. I want to create a matrix that compares multiple manufacturing metrics from two time periods for a list of equipment. Look below at the screenshot of the excel for a better idea. 

I have each of the metrics as measure, but I am struggling to create the Baseline, February, and Gain/Loss column. Ideally I could have two slicers above the visualization so the Baseline and Feb dates could be customized easily. (At that point I wouldn't call it Feb, my boss just wrote that in for the demo case) 

I have read as much as I can find and created a date and date2 table with the appropriate (I think) relationships but I cannot stack them side by side as headers for my columns, so now I am stuck. The headers are also called "2000" because I made the dates into bins of 100 years so that they would only appear once. I think this is not the right thing to do... Also so far I have only added good qty as my value just to try and get it to work, but then I will add the rest of my metrics. I have included my relationship table and where I currently am, and any help would be greatly appreciated!

Callan G

[cgarst]

Amit,

Thank you for your response!! I am watching the video, and am wondering how you created the TI table with the period measure with MTD, QT, and YTD. I think this is what I need to do but I am unsure of how to create it.

Callan

[v-easonf-msft]

Hi, @cgarst 

It seems that you want to dynamically compare the value of two period. 
If so, you can create an inactive relationship between you calendar tables.

Then you can filter data from  'Table2' instead of 'Table' by USERELATIONSHIP and REMOVEFILTERS.

Please check my attached sample .pbix file for more details.
Sample:

Sales1 = [Total_Sales]

Sales2 = 
CALCULATE (
    [Total_Sales],
    CALCULATETABLE (
        VALUES ( 'Date1'[Date] ),
        USERELATIONSHIP ( 'Date1'[Date], Date2[Date] ),
        REMOVEFILTERS ( 'Date1'[Date] )
    )
)

Gain/loss = [Sales1]-[Sales2]

If there is no relationship between your fact table and calendar tables, you can  try function 'TREATAS'.

Related thread:

how to compare multiple value in 2 different period of time 

Best Regards,
Community Support Team _ Eason

[cgarst]

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?

[v-easonf-msft]

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

[cgarst · Accepted solution]

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

[amitchandak]

@cgarst , I think the calculation group can help. Check my video on that can help

Learn Power BI Advance- Abstract Thesis Part 58:Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display: https://youtu.be/qMNv67P8Go0