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

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

  • 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