Forum Discussion

leodec11's avatar
leodec11
Frequent Visitor
11 months ago
Solved

2 individual matrix side by side

Hi

 

I built the 2 matrix visual, first matric showing previous year calculation, 2nd matrix showing current last 3 months calculation where it has dynamic column headers in format mmm-yy.

When  I tried to put them side by side, there is blank space coming, both matrixes are not aligned same way like tableau do. I tried changing the padding to 0, turned off boarders.

Structure of table is :- Hospital, previousyr, jun-25,jul-25,aug-25.

Is there any solution to show them as single table same as tableau does?

Thanks

 

 

Tips and Tricks

10 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    11 months ago

    Hi, 

    I am not sure how your desired output looks like, but I tried to create a sample pbix file like below.

    One of ways to achieve this is using Calculation Group.
    Please check the below link to Microsoft Learn, pictures and the attached pbix file.

     

     

    Create calculation groups in Power BI - Power BI | Microsoft Learn

     

     

     

    condition = 
VAR _previousyear =
    MAXX (
        FILTER ( ALL ( 'calendar' ), 'calendar'[Date] = TODAY () ),
        'calendar'[Year]
    ) - 1
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'calendar'[Year] ) = _previousyear
            && NOT ISINSCOPE ( 'calendar'[Month-Year] ), CALCULATE ( SELECTEDMEASURE (), 'calendar'[Year] = _previousyear ),
        ISINSCOPE ( 'calendar'[Month-Year] ),
            VAR _t =
                FILTER (
                    ALL ( 'calendar'[Month-Year sort], 'calendar'[Month-Year] ),
                    'calendar'[Month-Year sort] <= TODAY ()
                )
            VAR _last3months =
                WINDOW ( 1, ABS, 3, ABS, _t, ORDERBY ( 'calendar'[Month-Year sort], DESC ) )
            RETURN
                CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( _last3months ) )
    )

     

    • leodec11's avatar
      leodec11
      Frequent Visitor
      11 months ago

      Hi

      the sample data in table, and added reult expected in picture

      SectionClassRoomYearMnthDENONUMER
      class APclass A202506223
      class APclass A202506110
      class APclass A202506534
      class APclass A20250618
      class APclass A202506428
      class APclass A2025061034
      class APclass A202506228
      class APclass A202506330
      class APclass A202506856
      class APclass A20250611
      class APclass A202506148
      class Rclass A202506533
      class Rclass A202506976
      class Rclass A202507871
      class NMclass A202506217
      class NMclass A202506110
      class NMclass A202507227
      class NMclass A202507536
      class HEclass A202506550
      class HEclass A20250617
      class HEclass A202506330
      class HEclass A202506329
      class HEclass A202507110
      class Nclass A202506227
      class Nclass A202506110
      class Nclass A20250626
      class Nclass A202506110
      class Nclass A202506227

       . 

      I have built below column

      Report Date =
      DATE (
          DIVIDE ( 'test'[YearMnth], 100, 0 ),     -- Year
          MOD ( 'test'[YearMnth], 100 ),           -- Month
          1                                           -- First day
      )
      Measure :
      BaseRatio =
      DIVIDE (
          SUM ( 'test'[NUMER] ),
          SUM ( 'test'[DENO] )
      )
      Disconnected Table :- 
      Last3Months =
      VAR MaxMonth = EOMONTH(TODAY(), -1)  // last month
      RETURN
      ADDCOLUMNS(
          CALENDAR(EOMONTH(MaxMonth,-2)+1, MaxMonth),
          "MMM-YY", FORMAT([Date], "MMM-yy")
      )
      Calculation group :-
      condition =
      VAR _previousyear =
          MAXX (
              FILTER ( ALL ( 'calendar' ), 'calendar'[Date] = TODAY () ),
              'calendar'[Year]
          ) - 1
      RETURN
          SWITCH (
              TRUE (),
              MAX ( 'calendar'[Year] ) = _previousyear
                  && NOT ISINSCOPE ( 'calendar'[Month-Year] ), CALCULATE ( SELECTEDMEASURE (), 'calendar'[Year] = _previousyear ),
              ISINSCOPE ( 'calendar'[Month-Year] ),
                  VAR _t =
                      FILTER (
                          ALL ( 'calendar'[Month-Year sort], 'calendar'[Month-Year] ),
                          'calendar'[Month-Year sort] <= TODAY ()
                      )
                  VAR _last3months =
                      WINDOW ( 1, ABS, 3, ABS, _t, ORDERBY ( 'calendar'[Month-Year sort], DESC ) )
                  RETURN
                      CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( _last3months ) )
          )
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        11 months ago

        Hi,

        Share the download link of the Excel file with your formulas written there so that i can understand how you arrived at those results.  I will convert those formulas into DAX measures.

  • NimaiAhluwalia's avatar
    NimaiAhluwalia
    Continued Contributor
    11 months ago

    Hello leodec11 

     

    To better understand the request, can you please provide some visual images?

     

    As per my understanding, you can put all the measures in a single matrix visual, you don't need 2 matrix to show, unless it is a business requirement.

    • leodec11's avatar
      leodec11
      Frequent Visitor
      11 months ago

      Hi 

      thanks for replying.

       

      table structure is 

      Specialty | PrevRatio | Jun-25 | Jul-25 | Aug-25 | CurrentRatio |, 

      the major problem is last 3 months dynamic headers,  DAX measure built to show last 3 months ratio like this 

      DynamicRatio =
      VAR OffsetMonths = SELECTEDVALUE(Last3Months[Offset])
      VAR StartDate = EOMONTH(EOMONTH(TODAY(), -1), OffsetMonths) - DAY(EOMONTH(EOMONTH(TODAY(), -1), OffsetMonths)) + 1
      VAR EndDate = EOMONTH(EOMONTH(TODAY(), -1), OffsetMonths)
      VAR Num = CALCULATE(SUM('Query1'[RESULTVALNUMERATOR]), 'Query1'[Report Date] >= StartDate, 'Query1'[Report Date] <= EndDate)
      VAR Den = CALCULATE(SUM('Query1'[RESULTVALDENOMINATOR]), 'Query1'[Report Date] >= StartDate, 'Query1'[Report Date] <= EndDate)
      RETURN IF(Den <> 0, ROUND(DIVIDE(Num, Den), 3))
       
      Last3Months is disconnected table to show column header dynamically in MMM-YY format
       
      Hope it helps
      Regards
  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    Hi leodec11 ,

    Thanks Jihwan_Kim  for sharing the detailed calculation group solution with screenshots and a pbix file. This approach should resolve the alignment issue you faced with two separate matrices, since everything can now be handled in a single matrix visual with dynamic MMM-YY headers.

    leodec11 did you get a chance to check out Jihwan’s solution? It looks like it should meet your requirement of

    Specialty | PrevRatio | Jun-25 | Jul-25 | Aug-25 | CurrentRatio

    all in one matrix without spacing problems.

    If you are still unable to achieve the expected result, could you please share a small set of sample data along with some screenshots and, if possible, a pbix file? That will make it easier for the community to reproduce your scenario and guide you with a precise fix.

    Thanks,
    Akhil.