Forum Discussion

VAthavale's avatar
VAthavale
Frequent Visitor
6 years ago
Solved

Show Sub Total on Row

Hello,

 

I need to show subtotal on following rows. 

Need Help
Show and Tell
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    According to your description, I create a table to test:

    Then, create a column to calculate the total amount and put it in the row fields:

    Total Amount by Product = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Products],'Table'[Work Order Number]))

    the reason why Power BI Desktop does not calculate the sum of total amount for each [Work Order Number] is that because this column is in the rows fields and you should put it in values fields.

     

    So, I put this calculated column in values field, and it shows:

    Here is my test pbix file:

    pbix 

     

    Best Regards,

    Giotto Zhi

  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    For your problem, I use a new table to test:

     

    Please take following steps:

    1)Create a measure to replace the column [TotalAmountByProduct]:

    Total Amount by Product = SUM('Table'[Amount])

     2)Put this measure into Values field and it shows:

    3)Turn off 'Word Wrap' in Format->Column headers and hide this measure under each resources, it shows:

     

    Best Regards,

    Giotto Zhi

6 Replies

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    According to your description, I create a table to test:

    Then, create a column to calculate the total amount and put it in the row fields:

    Total Amount by Product = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Products],'Table'[Work Order Number]))

    the reason why Power BI Desktop does not calculate the sum of total amount for each [Work Order Number] is that because this column is in the rows fields and you should put it in values fields.

     

    So, I put this calculated column in values field, and it shows:

    Here is my test pbix file:

    pbix 

     

    Best Regards,

    Giotto Zhi

    • VAthavale's avatar
      VAthavale
      Frequent Visitor
      6 years ago

      Thank you for your reply. I created new column. But my requirement is as follows:

      1. I need to show

      i. Work Order Number

      ii. Products per Work Order Number

      iii. Total Amount

      iv. Resources worked on it

      v. Bonus per Resource

      vi. Total Bonus

      vii. % Loss or Profit per Work Order Number

       

      Therefore I have Resources in Columns. If I add Total Amount in Values it gets devided per resource and I dont want that. Kindly help me with this.


       

      • v-gizhi-msft's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        Hi,

         

        For your problem, I use a new table to test:

         

        Please take following steps:

        1)Create a measure to replace the column [TotalAmountByProduct]:

        Total Amount by Product = SUM('Table'[Amount])

         2)Put this measure into Values field and it shows:

        3)Turn off 'Word Wrap' in Format->Column headers and hide this measure under each resources, it shows:

         

        Best Regards,

        Giotto Zhi