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
v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

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

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

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

        Hi mibu , Just paging mfelix for his viewpoint.