Sub Total Calculation

[Sugumaran31]

Hi Guys,

I need help from you Guys. I need to calculate the subtotal. please refer to the below images

eg: 1,2,3,....,12 is a serial no.

Based on Serial No, I need to add a measure or column 

2+3 =4  ( totalA.revenue)

7+8+9+10+11=12 ( totalB.expenc)

I try but it gives zero values 

[Anonymous]

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.

[Sugumaran31]

hi @Anonymous it's working in Chrome not in Microsoft Edge

try it in Chrome

[Sugumaran31]

Hi @Anonymous it's working in the column, but when I put in the visual it not working. I will share the image below

[amitchandak]

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

[Sugumaran31]

But Dax is not working sir I tried it. I will share the Excel file link in the below belowhttps://docs.google.com/spreadsheets/d/1rx6lbBi98NEIKPdRjJf6NFaxk6c_6-Vs/edit?usp=drive_link&ouid=10...