Numerous Subtotals Formula

[mibu]

Hey Everybody!

I have the following data:

Type / Units

A / 100

B / 200

C / 300

Is there a formula that will allow me to show the following in a Matrix:

Type/Units

A / 100

B / 200

C / 300

A+B / 300

A+B+C / 600

Thanks in advance!

mibu

[v-xicai]

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.

[mibu]

Hi @v-xicai 

I did per your guidance and get the following result:

What I need however is for the Matrix to read:

Cumulative total / 3600

I need it to show as a row, not a column in the Matrix.

Thoughts?

Thanks!

mibu

[mibu]

Hi @v-xicai 

I am looking for a Measure that will allow me to create the following:

Sum of A+B

Sum of A+C...

THEN...  be able to pull them both into Row section of a single Matrix.

Matrix will automally Grand Total A+B+C.

Including other contributors to this thread:

@Nathaniel_C 

@MFelix 

Thanks Team!

[Nathaniel_C]

Hi @mibu , Just paging mfelix for his viewpoint.

[mibu]

Hi @Nathaniel_C

Ah...  very good, I am just working on @MFelix  proposed solution.

very best, mibu

[mibu]

@v-xicai Thanks for your post!  I am currently reviewing your proposed solution.  very best, mibu

[Nathaniel_C]

@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))

[mibu]

Hi @Nathaniel_C 

Expanding the scope a bit...  to be more in line with the problem I am trying to solve.

Here is a faux sample set that resembles the eight variables I am working with...  you can see your A+B and A+B+C solutions in Column AB/ABC:

Here is the output and matrix format I am trying to achieve:

Thanks in advance for your feedback about trying to solve this problem.  I am aware that Subtotals/Row subtotals toggled On will sum Grand Total.

Thanks!

mibu

[Nathaniel_C]

Hi @mibu ,
What are you trying to do? This looks like something that you won't be able to solve in Power BI alone. Might you provide us with a pbix. I think you will need to create other tables as well.
Thank you,

Nathaniel

[mibu]

Hi @Nathaniel_C 

Thanks for your reply.

A and B share a parameter...  call it X.

C, D and E share a parameter...  call it Y.

F stand alone...  its parameter, we will call it F as well.

G and H share a parameter...  call it Z.

I know by creating a Dim table relating A-H  to  X,Y,F,Z...  I can show A-H and Subtotals X, Y, Z and Grand Total in a Table.  But, I like the formatting in Matrix...  so I am looking to find a Meausre that allows me to Subtotal and show X, Y and Z in a Matrix...  along with A through H.  PLUS, I want to Subtotal X+Y, AND X+Y+F and show them in the very same Matrix as well.

Does that make sense?

Thanks so much.

Very best,

mibu

[Nathaniel_C]

@MFelix ?

[mibu]

Hi @Nathaniel_C

I'm sorry, are you replying to a reply of @MFelix ?  I don't see a reply from him beyond his first post.  Just want to make sure I am not missing something.  Thanks.  mibu

[mibu]

@Nathaniel_C Thanks for your post!  I am currently reviewing your proposed solution.  very best, mibu

[MFelix]

Hi @mibu ,

Create a new table with the following format

CAT       ID

A	1
B	2
C	3
A+B	4
A+B+C	5

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]

Hi @MFelix 

Is it possible to approach the problem in this way:

Create a new table as follows (table and .pbix file ABCTEST uploaded to dropbox here

And then come up with a Calculation/Measure/Formula that allows us to show the following in a single MATRIX:

A through H… each in its own Row (alphabetically)…

A+B… in its own Row (below row B)…

C+D+E … in its own Row (below row E)…

A+B+C+D+E...  in its own Row (below C+D+E Subtotal row)...

A+B+C+D+E+F...  in its own Row (below row F)...

G+H… in its own Row (below row H)…

Thanks!

Mibu

@Nathaniel_C 

@v-xicai 

[mibu]

Hi @MFelix pls see updated message 17.  Thanks!

@Nathaniel_C 

[MFelix]

Hi @mibu,

Have been sick for the last days. Will check this and get back to you ASAP.

Sorry for the delay.

Regards,
MFelix

[mibu]

Sorry the hear that @MFelix 

Thanks for the udpate!  Look forward to hearing back from you!

Best,

mibu

@Nathaniel_C 

[MFelix]

Hi @mibu,

Make the following change in the table CAT:

CATID

A	1
B	1
A+B	2
C	3
D	3
E	3
C+D+E	4
F	5
A+B+C+D+E+F	6
G	7
H	7
G+H	8

Now add the following measure to your model

CALCULATION_ =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( CATID[ID] ) = 1; CALCULATE ( SUM ( ABCTEST[UNITS] ); ABCTEST[CONFIG] = VALUES ( CATID[CAT] ) );
    SELECTEDVALUE ( CATID[ID] ) = 2; CALCULATE (
        SUM ( ABCTEST[UNITS] );
        FILTER ( ALL ( ABCTEST ); ABCTEST[CONFIG] IN { "A"; "B" } )
    );
    SELECTEDVALUE ( CATID[ID] ) = 3; CALCULATE ( SUM ( ABCTEST[UNITS] ); ABCTEST[CONFIG] = VALUES ( CATID[CAT] ) );
    SELECTEDVALUE ( CATID[ID] ) = 4; CALCULATE (
        SUM ( ABCTEST[UNITS] );
        FILTER ( ALL ( ABCTEST ); ABCTEST[CONFIG] IN { "C"; "D"; "E" } )
    );
    SELECTEDVALUE ( CATID[ID] ) = 5; CALCULATE ( SUM ( ABCTEST[UNITS] ); ABCTEST[CONFIG] = VALUES ( CATID[CAT] ) );
    SELECTEDVALUE ( CATID[ID] ) = 6; CALCULATE (
        SUM ( ABCTEST[UNITS] );
        FILTER ( ALL ( ABCTEST ); ABCTEST[CONFIG] IN { "A"; "B"; "C"; "D"; "E"; "F" } )
    );
    SELECTEDVALUE ( CATID[ID] ) = 7; CALCULATE ( SUM ( ABCTEST[UNITS] ); ABCTEST[CONFIG] = VALUES ( CATID[CAT] ) );
    SELECTEDVALUE ( CATID[ID] ) = 8; CALCULATE (
        SUM ( ABCTEST[UNITS] );
        FILTER ( ALL ( ABCTEST ); ABCTEST[CONFIG] IN { "G"; "H" } )
    )
)

 This can probably be simplified, but since I took so long to answer you I think is better to share this result.

I will try to improve this later on but this is working currently.

Regards,

MFelix

[mibu]

Hi @MFelix

Apologies for delay in getting back to you!

I am trying to recreate your solution.

I copied your formula straight from Forum and am seeing the following error message.

Thoughts?  Thanks!

@mibu