Forum Discussion

mibu's avatar
mibu
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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 / ...
Need Help
Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
mibu
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

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's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      mibu
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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