How to display management accounts with correct subtotals

[masplin]

igse for posting this again, but I got an answer that i acepted as a solution, but turned out the solution was wrong and had exactly the same problem I experienced. Hopnig someone else might know how to solve it

Was reading this post about how to get acconting data to dispaly correctly in powerpivot drill-income-statement-design but can't get it working in Power BI as doesn't show any of the entries fror EBITDA etc only the ones that are the sum of some defined rows e.g. 

Total Marketing = CALCULATE([Total Actual],Budget_L1[Budget L1]="Marketing")

whereas EBITDA is defined as

EBITDA = [Total GP test]+[Total Staff Costs]+[Total Marketing]+[Total Overheads]

where each component has same form as the Marketing one

So is there some limitation in PBI that the SWITCH function doesn't work the same?  

Is there some other way to show a matrix that includes both regualr pivottable like sums and also calcuated measures?  The article seemed to be the answer , but can't work out why it doesn't work!!!

Thanks for any advice or alternative suggestions

I have since tried the following tests

For what its worth I have tried a few experiments in Power BI as this is really frustrating.

I set up my Header table like this

Budget L1 IDBudget L1SummaryShow Detail

1	Revenue	1
2	Marketing	1
3	Total Staff Costs	1	1
4	Fixed Overheads	1	1
5	Variable Overheads	1	1
6	Total Overheads	1
7	EBITDA	1
8	Depreciation & Amortisation	1	1
9	Net Contribution	1
10	Exceptional	1	1
11	Net Profit	1
16	Balance Sheet	1

My IsSubtotal measure is

IS Subtotal Act =
IF (
COUNTROWS ( VALUES ( Budget_L1[Budget L1] ) ) = 1,
SWITCH (
VALUES ( Budget_L1[Budget L1 ID] ),
1, [Total GP test],
2, [Total Marketing],
3, [Total Staff Costs],
4, [Total Fixed Overheads],
5, [Total Variable Overheads],
6, [Total Overheads],
7, [EBITDA],
8, [Total Depreciation & Amortisation],
9, [Net Contribution],
10, [Total Exceptionals],
11, [Net Profit],
BLANK ()
),
100
)

where Total Fixed Overheads = CALCULATE([Total Actual],Budget_L1[Budget L1]=”Fixed Overheads”)

and Total Overheads= [Total Fixed overheads]+[Total Variable Overheads]

as reported this gives data in the power bi report for the components, but no entry for the total one that sums the 2 measures as below

I then tried replacing the Total Overheads with CALCULATE([Total Actual],Budget_L1[Budget L1]=”Fixed Overheads”)+CALCULATE([Total Actual],Budget_L1[Budget L1]=”Variable Overheads”). This made no difference

I then tried changing the formula for Total Fixed Overheads = CALCULATE([Total Actual],Budget_L1[Budget L1]=”Variable Overheads”). I thoguht I woudl just get a duplicate value for both fixed and variable overheads in the power BI report, but actually the Fixed one just disappeared.

I don’t really understand the code, but seems in Power BI it is critical that the formula refers to the Budget L1 tha t is in the head table and if they aren’t the same you get no result. Is this because of the COUNTROWS bit?

Mike

Mike

[v-chuncz-msft]

@masplin,

You may check if the following thread helps.

http://community.powerbi.com/t5/Desktop/Utilization-Consolidated-summary-both-horizontal-and-vertica...

[masplin]

sorry no as this is basically subtotals of the same measure not showing intermediate totals based on a different measure which is what the blog post was trying to do.