calculate subtotal for each category using measure

[Anonymous]

Hi All,

Below is my sample data,

 You may have seen this Structure in Microsoft Dynamics NAV in Chart of Accounts.

I need to create a trial balance report in Power BI. My Visual is a Table. I want to derive the last column (sum amount) as measure in my table visual. 

'Total' column represents the start and end range of 'No_'. I need to calculate the sum of amount (highlighted in red color) between the specified ranges in 'total' column.

Main category to sub-categories are:

1) Liabilities  & Equities

      i) Liabilities

            a) Loans

            b) General Reserves

      ii) Equities

            a) shares

To create a subtotal for each category , where the acoount type is specified as 'end total' using dax measure.

Thanks in Advance....

[Anonymous · Accepted solution]

I found the solution,

Create another table (Master Table) without amount and date column (if you have it in same table).

Also should not give any relationship in model view.

And create the below measures there, 

total= CALCULATE(SUM('Table1'[Amount]),filter(values('Table1'[No_]), 'Table1'[No_] >= min('Master Table'[start range]) && 'Table1'[No_] <= max('Master Table'[end range])))

Sum Amount=sum(Table1[Amount]+[total])

[amitchandak]

@Anonymous , You have add additional column in table to group those. or do segmentation

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/

Refer this raditional Financial Statements

https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223