Numerous Subtotals Formula

Hi mibu ,

Create a new table with the following format

CAT       ID

Now add the following measure to your model.

CALCULATION =
SWITCH (
    TRUE ();
    MAX ( CAT[ID] ) < 4; CALCULATE ( SUM ( 'Table'[Units] ); 'Table'[Type] = VALUES ( CAT[CAT] ) );
    MAX ( CAT[ID] ) = 4; CALCULATE (
        SUM ( 'Table'[Units] );
        FILTER ( ALL ( 'Table'[Type] ); 'Table'[Type] IN { "a"; "B" } )
    );
    CALCULATE ( SUM ( 'Table'[Units] ); ALL ( 'Table'[Type] ) )
)

 Then use the previous table information and the measure to create your matrix.

Regards,

MFelix

mibu ,

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Sum A+ B, or sum A+B+C = 
var _A = Calculate(SUM(myTable[Units]),ALLEXCEPT(myTable,myTable[Type ]),myTable[Type ]= "A")
var _B = Calculate(SUM(myTable[Units]),ALLEXCEPT(myTable,myTable[Type ]),myTable[Type ]= "B")
var _C = Calculate(SUM(myTable[Units]),ALLEXCEPT(myTable,myTable[Type ]),myTable[Type ]= "C")


//var _AB = (myTable[Type ] = "A"|| myTable[Type ] = "B", myTable[Units]))
return
IF(MAX(myTable[Type ])="B",_A+_B, If(MAX(myTable[Type ]) = "C", _A+_B+ _C))

Hi mibu ,

You can create measure to get cumulative total like DAX below, then put it into Values box of Matrix visual.

Cumulative total = CALCULATE(SUM(Table1[Units]),FILTER(ALLSELECTED(Table1), Table1[Type] <=MAX(Table1[Type])))

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.