Forum Discussion

rajasekar_o's avatar
rajasekar_o
Helper V
2 years ago
Solved

sub total calculation

hi team , i need help ,
need to calculate sub total , i give the data and expected output

output

i try but it gives cumulative total only 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, rajasekar_o 

    I'm happy to answer your questions. We can find the subtotal in the following two ways:

    We can use measure to achieve the effect of finding subtotals:

    Sample data:

    DAX formula is as follows:

     

     

     

    Total A.revenue = CALCULATE(SUM(Sheet5[Amt]),'Sheet5'[item] IN {"Revanue from opration","Other"})
Total B.cost = CALCULATE(SUM(Sheet5[Amt]),'Sheet5'[item] IN {"Cost of meirial","Purchase"})

     

     

     

    We can use cards to reflect the effect:

     

     

    Method 2, we can find the subtotal in power query:

    First copy our table:

     

    Remove first column:

    pivot column:

    Add custom columns:

    replace null value:

    Next, we unpivot the column:

    Then we replace the value:

     

     

    Finally we add the index column and rename it and move it to the first column:

     

     

     

    Close power query and apply. I have provided my PBIX file below. If this helps you, that would be great

     

     

     

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    Hi rajasekar_o 

    In your table, create a calculated column 

    Category = 
SWITCH(
TRUE(),
'TableName'[Column] IN {"Revenue from operation","Other Income"},"A.Revenue",
'TableName'[Column] IN {"Cost of material","Purchase"},"B.Expense")

     

    use the column in 1st level row header in Matrix visual

    If not work, please share sample of your exisiting data format, relationship

    If solved, please mark as SOLUTION

    • rajasekar_o's avatar
      rajasekar_o
      Helper V
      2 years ago

      s.no

      		 Amt 
      1A.REVANUE  
      2Revanue from opration 9000 
      3Other Income2000 
      4B.EXPENCE   
      5Cost of metirial 30000 
      6purchase 35000 

      calculate total for A. REVENUE AND B.EXPENCE

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, rajasekar_o 

    I'm happy to answer your questions. We can find the subtotal in the following two ways:

    We can use measure to achieve the effect of finding subtotals:

    Sample data:

    DAX formula is as follows:

     

     

     

    Total A.revenue = CALCULATE(SUM(Sheet5[Amt]),'Sheet5'[item] IN {"Revanue from opration","Other"})
Total B.cost = CALCULATE(SUM(Sheet5[Amt]),'Sheet5'[item] IN {"Cost of meirial","Purchase"})

     

     

     

    We can use cards to reflect the effect:

     

     

    Method 2, we can find the subtotal in power query:

    First copy our table:

     

    Remove first column:

    pivot column:

    Add custom columns:

    replace null value:

    Next, we unpivot the column:

    Then we replace the value:

     

     

    Finally we add the index column and rename it and move it to the first column:

     

     

     

    Close power query and apply. I have provided my PBIX file below. If this helps you, that would be great

     

     

     

     

     

     

    How to Get Your Question Answered Quickly

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Jianpeng Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.