topic Re: calculate subtotal for each category using measure in DAX Commands and Tips

calculate subtotal for each category using measure

Anonymous — Thu, 07 Jan 2021 10:25:54 GMT

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....

Re: calculate subtotal for each category using measure

amitchandak — Thu, 07 Jan 2021 11:11:57 GMT

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

Re: calculate subtotal for each category using measure

Anonymous — Wed, 13 Jan 2021 16:41:38 GMT

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])