Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mibu
Employee
Employee

Numerous Subtotals Formula

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

22 REPLIES 22
v-xicai
Community Support
Community Support

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.

Hi @v-xicai 

I did per your guidance and get the following result:

Capture.PNG

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

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!

Hi @mibu , Just paging mfelix for his viewpoint.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C

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

very best, mibu

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

Nathaniel_C
Super User
Super User

@mibu ,



ab.PNG

 


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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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:

Capture2.PNG

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

Capture2.PNG

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

@MFelix ?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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

MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Capture.PNG

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 

Hi @MFelix pls see updated message 17.  Thanks!

@Nathaniel_C 

Hi @mibu,

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

Sorry for the delay.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Sorry the hear that @MFelix 

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

Best,

mibu

@Nathaniel_C 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Error Msg 1.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.