Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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:
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:
Thanks Team!
Hi @mibu , Just paging mfelix for his viewpoint.
Proud to be a Super User!
@v-xicai Thanks for your post! I am currently reviewing your proposed solution. very best, mibu
@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))
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:
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
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
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 ?
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |