Sub Total Calculation

Sugumaran31 , Make sure Desc and PROW are coming from a dim table

 

example

Amount =
Switch( True()
, MAX ( Account[Account ID] ) = 4, CALCULATE([Revenue], filter(all(Account), Account[Account ID] in {1,2,3} ))
, MAX ( Account[Account ID] ) = 10, CALCULATE([Revenue], filter(all(Account), Account[Account ID] in {8,9,10} ))
[Total Amount]

)

 

 

Have explained the same in, additional table is optional , I used as I do not want change account table

 

Power BI How to get the P&L formatting right: https://youtu.be/C9K8uVfthUU
Power BI How to get two columns format Profit and Loss Statement(P&L) right: https://youtu.be/WLg85yiMgHI
https://medium.com/microsoft-power-bi/power-bi-formatted-p-l-with-custom-sub-totals-and-blank-rows-e39ca822908a

Hi Sugumaran31 ,

The link to the dataset you provided could not be opened.

Also, for the situation you are facing, you can try the following DAX to create a new column:

 

Total = 
IF(
    'Table'[PDESCR] = "Total A.REVENUE",
    SUMX(FILTER('Table', 'Table'[Index] >= 2 && 'Table'[Index] <= 3), 'Table'[Value]),
    IF(
        'Table'[PDESCR] = "Total B.EXPENSE",
        SUMX(FILTER('Table', 'Table'[Index] >= 7 && 'Table'[Index] <= 11), 'Table'[Value]),
        'Table'[Value]
    )
)

 

The final output is as below:

 

However, I would still like to remind you that your data structure can lead to extremely complex calculations when there is a large amount of data, so please change to a more convenient data logging model.

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