How To get the Cumulative Mix for Matrix table

Hi Roshan_201295 

Calculating cumulative mix of accounts by MOB where:

• MOB = Difference between 'Closure Month' and 'Disb Month' in months
• You want Disb Month as row and MOB as columns in a matrix
• Each column should accumulate mix (%) from MOB 0 to that MOB

Here’s a complete step-by-step approach:

Add a Calculated Column for MOB

MOB =”MOB”& DATEDIFF('Data'[Disb Month], 'Data'[Closure Month], MONTH)

Create the Cumulative Mix (%) Measure

Cumulative Mix (%) =
VAR CurrentMOB = MAX('Data'[MOB])
VAR CurrentDisb = MAX('Data'[Disb Month])
VAR Numerator =
  CALCULATE(
      SUM('Data'[NO_OF_ACCOUNTS]),
      FILTER(
          ALL('Data'),
          'Data'[Disb Month] = CurrentDisb &&
          'Data'[MOB] <= CurrentMOB
      )
   )
VAR Denominator =
  CALCULATE(
      SUM('Data'[NO_OF_ACCOUNTS]),
      FILTER(
          ALL('Data'),
          'Data'[Disb Month] = CurrentDisb
      )
   )
RETURN
DIVIDE(Numerator, Denominator)

Build the Matrix Visual

Set it up like this:

• Rows → Disb Month
• Columns → MOB
• Values → Cumulative Mix (%)

Hi Roshan_201295 ,

To achieve your goal of calculating a cumulative mix in a matrix, you'll need to create one calculated column and two DAX measures. The overall strategy involves first defining the "Month on Book" (MOB) for each transaction, then calculating a base percentage for each MOB, and finally, creating a measure that calculates a running total of that base percentage across the columns of your matrix.

The foundational step is to create a calculated column in your Data table that computes the MOB value as a number. This column is essential because it will serve as the header for the columns in your matrix visual. You can add this column using the following DAX expression, which accurately determines the number of months between the disbursement and closure dates. Ensure its data type is set to "Whole Number" for the subsequent calculations to work correctly.

MOB Number = 
VAR DisbMonthSerial = YEAR(Data[Disb Month]) * 12 + MONTH(Data[Disb Month])
VAR ClosureMonthSerial = YEAR(Data[Closure month]) * 12 + MONTH(Data[Closure month])
RETURN
ClosureMonthSerial - DisbMonthSerial + 1

Next, you must create a base measure that calculates the simple, non-cumulative percentage mix for each individual cell in the matrix. This measure will serve as the building block for the final cumulative calculation. The DAX formula below calculates the number of accounts in the current context and divides it by the total number of accounts for that entire disbursement month row. The ALLEXCEPT function is key here, as it establishes the correct denominator by keeping the filter on Disb Month while removing the filter from the MOB Number.

Base Mix % = 
VAR AccountsInCurrentContext = SUM(Data[NO_OF_ACCOUNTS])
VAR TotalAccountsForDisbMonth = 
    CALCULATE(
        SUM(Data[NO_OF_ACCOUNTS]),
        ALLEXCEPT(Data, Data[Disb Month])
    )
RETURN
    DIVIDE(AccountsInCurrentContext, TotalAccountsForDisbMonth, 0)

Finally, you can write the measure that produces the cumulative result you need. This measure calculates a running total of the [Base Mix %] measure. It works by first identifying the maximum MOB Number in the current column's context. It then uses CALCULATE to modify the filter context, summing the [Base Mix %] for all MOB Number values that are less than or equal to the MOB of the current column. This creates the horizontal cumulative effect seen in your example image.

Cumulative Mix % = 
VAR MaxMOB_InCurrentContext = MAX(Data[MOB Number])
RETURN
    CALCULATE(
        [Base Mix %],
        FILTER(
            ALLSELECTED(Data[MOB Number]),
            Data[MOB Number] <= MaxMOB_InCurrentContext
        )
    )

To build the visual, place Data[Disb Month] on the Rows, the Data[MOB Number] calculated column on the Columns, and your new [Cumulative Mix %] measure in the Values field of a matrix visual. After formatting the measure as a percentage, the matrix will display the cumulative mix exactly as you require.

Best regards,

Hi Roshan_201295,

Thank you  for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank DataNinja777 , rohit1991 for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

based on your requirement to calculate cumulative mix % across MOBs in a matrix with Disbursement Month on rows, you can achieve this by first calculating base mix and then using a cumulative DAX measure with MOB filters.

I tested it with my sample data, and it worked fine. Please find the attached screenshot and Pbix for your reference.

Hope this helps if you have any queries we are  happy to assist you further.
Best Regards,
Harshitha.