Forum Discussion

cgarst's avatar
cgarst
Regular Visitor
4 years ago
Solved

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 ma...
Need Help
  • cgarst's avatar
    cgarst
    4 years ago

    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

cgarst's avatar
cgarst
Regular Visitor
4 years ago

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's avatar
    v-easonf-msft
    Community Support
    4 years ago

    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's avatar
      cgarst
      Regular Visitor
      4 years ago

      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's avatar
        v-easonf-msft
        Community Support
        4 years ago

        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