Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
cgarst
Regular Visitor

Create a Matrix to Compare Multiple Metrics under Two Time Periods

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. 

cgarst_0-1646085591653.png

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_1-1646085857058.png

cgarst_2-1646085925435.png

 

 

1 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!!

cgarst_0-1647287824693.pngcgarst_1-1647287870027.pngcgarst_2-1647287889315.png

cgarst_3-1647287944028.png

 

cgarst_4-1647287975577.pngcgarst_5-1647288001016.pngcgarst_6-1647288022446.png

 

cgarst_7-1647288043942.png

Hope this helps people in the future!

Callan

View solution in original post

6 REPLIES 6
cgarst
Regular Visitor

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

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

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.

12.png

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)]

13.png

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!!

cgarst_0-1647287824693.pngcgarst_1-1647287870027.pngcgarst_2-1647287889315.png

cgarst_3-1647287944028.png

 

cgarst_4-1647287975577.pngcgarst_5-1647288001016.pngcgarst_6-1647288022446.png

 

cgarst_7-1647288043942.png

Hope this helps people in the future!

Callan

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.