Sub total between rows in Financial tables (in Pivot tables)

[muratakkemik]

I have an excel sheet which i'm trying to take  P&L Setup and B/S setup. I can bring the all accounts with interval match but I could not bring the "sub total" lines according to between RowFrom and RowTo columns. I'm adding my Excel file.

I'm using in my expression but it does not help, can some one send me a sample?

if(RowType = 'Regular',Sum({<[Fiscal Year]={$(vYTDYear)} >} Amount),
if(RowType = 'Sum',aggr(Sum({<[Fiscal Year]={$(vYTDYear)}, RowNo= {"<=RowTo"}>}Amount), ReportRowGroup)
))

Regular part is coming but Sum parts are not coming? Please advise

[alex00321]

Hi this is a topic I interested, and I took some time to research. The way I resolve it is kind of "Manuel". Without total, seems we could not make it. I use total to create first 2 "Sum" dimensions, you could see if this helps. Thanks!

[muratakkemik]

is there way for having subtotals according to row numbers? For in my example, 1st subtotal is calculated row 1 to 10, second one 1 to 12 ....? I saw this solution in somewhere but i could not find it again ;( 

[muratakkemik]

I added 3rd one in red color (Net Revenue) but it did not appear on screen, am I wrong some where?

if(RowType = 'Regular',Sum(Amount),
if(RowType = 'Sum',
Pick(Match(ReportRowGroup,'Gross Revenue', 'Net Revenue', 'Net Revenue'),
Sum(TOTAL {<ReportRowGroup={'Revenue'}>}Amount),
Sum(TOTAL {<ReportRowGroup={'Discount (Given)'}>}Amount),
Sum(TOTAL {<ReportRowGroup={'Revenue'}, ReportRowGroup={'Discount (Given)'}>}Amount)
,1)))

[alex00321]

Hi you could not put two identical value in Match part, how you could set the value for it? Thanks!

[muratakkemik · Accepted solution]

I can solve the problem like below.

I put all subtotal formulas in variables then i used below formula in chart expression;

if(RowType = 'Regular',Sum(Amount),
if(RowType = 'Sum',
Pick(Match(ReportRowGroup,'Gross Revenue','Net Revenue', 'Gross Profit I', 'Gross Profit II', 'EBITDA', 'EBIT', 'EBT', 'Net Profit'),
$(vPL_GrossRevenue),
$(vPL_NetRevenue),
$(vPL_GrossProfit1),
$(vPL_GrossProfit2),
$(vPL_Ebitda),
$(vPL_Ebit),
$(vPL_Ebt),
$(vPL_NetProfit)
,1)))

I'm also adding here one of variable formula too;

 for vPL_GrossRevenue , =Sum(TOTAL {<ReportRowGroup={'Revenue'}>}Amount)

for $(vPL_NetRevenue), =Sum(TOTAL {<ReportRowGroup={'Discount (Given)'}>}Amount) + $(vPL_GrossRevenue)

For $(vPL_GrossProfit1), =Sum(TOTAL {<ReportRowGroup={'COGS'}>}Amount) + $(vPL_NetRevenue)

....