How To get the Cumulative Mix for Matrix table

[Roshan_201295]

Hi Everyone,

I'm facing an issue while of calculating the cummulative mix in matrix table like below way 

I have mix formuale like 

Mix (%) =

Var a = CALCULATE(sum(Data[NO_OF_ACCOUNTS]),all(Data))

Var b = sum(Data[NO_OF_ACCOUNTS])

return

divide(b,a,0)

But i want this formuale in structural way of matrix table MOB will be at the Column level and Disb Month in row level 

---(MOB Wise) in that MOB = Diffrence between Disb Month and Closure month so in that case MOB-1 mix  and MOB-2 will gives RESULT LIKE MOB-1 + MOB2 Mix.

--- Disb. Month is in the date format.

Kindly let me know how to get the above kind of Cumulative mix and also provide code and ideaa how to calculate that one

Dax required Thanks !

[Roshan_201295]

This problem has been resolved Thanks !

[v-hjannapu]

Hi @Roshan_201295,
Thank you for sharing the update. If the issue has been resolved, please mark the appropriate response as the solution so other community members can find it more easily.

Best Regards,
Harshitha.

[Ashish_Mathur]

Hi,

Share a sample of the data table and for that sample data, show the expected result.

[v-hjannapu]

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.

[v-hjannapu]

Hi @Roshan_201295,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

Regards,
Harshitha.

[v-hjannapu]

Hi @Roshan_201295,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Harshitha.

[v-hjannapu]

Hi @Roshan_201295,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[v-hjannapu]

Hi @Roshan_201295,

I wanted to follow up and see if you have had a chance to review the information that was shared. If you have any additional questions or need further clarification, please don’t hesitate to reach out. I am here to assist with any concerns you might have.

Regards,
Harshitha.

[DataNinja777]

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,

[rohit1991]

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 (%)