sub total calculation

[rajasekar_o]

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 

[Anonymous · Accepted solution]

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.

[PijushRoy]

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.no		Amt
1	A.REVANUE
2	Revanue from opration	9000
3	Other Income	2000
4	B.EXPENCE
5	Cost of metirial	30000
6	purchase	35000

calculate total for A. REVENUE AND B.EXPENCE