Calculate function on Revenue/expense fields

[danhendo888]

Append Power Query P05'24 - PQ Test.xlsx
	H	I	J	K	L	M	N	O	P	Q
4			Sum		FilteredGrandtotal		&ofFilteredGrand		Correct %
5	Period	Country	R	E	R	E	R	E	R	E
6	P01	AUSTRALIA	(281,367.56)	226,539.96	(287,648.55)	(287,648.55)	97.82%	-78.76%	27.27%	30.44%
7		HONG KONG	(446,573.35)	290,908.66	(287,648.55)	(287,648.55)	155.25%	-101.13%	43.28%	39.10%
8		INDIA	(259,333.60)	200,936.02	(287,648.55)	(287,648.55)	90.16%	-69.85%	25.14%	27.00%
9		INDONESIA	(44,477.33)	25,718.65	(287,648.55)	(287,648.55)	15.46%	-8.94%	4.31%	3.46%
10	P01 Total		(1,031,751.84)	744,103.29	(287,648.55)	(287,648.55)	358.68%	-258.68%	100.00%	100.00%
11
12
13		Sum of R and E:	(287,648.55)
Start

Cell Formulas
Range	Formula
P6:Q10	P6	=J6/J$10
J13	J13	=SUM(J10:K10)

FilteredGrandtotal:=CALCULATE([Sum],ALLSELECTED())
&ofFilteredGrand:=DIVIDE([Sum],[FilteredGrandtotal])

Suppose I have revenue and expense data as below (above is the pivot table from the data model which I've filtered for select countries, for a select month)
And I want to put revenue as a percentage of the filtered revenue total $-1,031,751.84. And expense as a percentage of the filtered expense total $744,103.29

Currently, it is putting revenue as a percentage of the filtered revenue + expense total ($-1,031,751.84 + $744,103.29 = $-287,648.54) and
also putting expense as a percentage of the filtered revenue + expense total ($-1,031,751.84 + $744,103.29 = $-287,648.54)

How would I go about this?

My data looks like this:

R/E	Amount
E	100
R	-10
R	-50
E	20
E	50

 

[Matt Allington]

Your filtered grand total amount combines both revenue and expense into a single total (and incorrectly uses ALLSELECTED), yet you indicate you want calculations as a percentage of R and E separately. Also, to haven’t indicated if you have a star schema set up; you definitely should do this.

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

You need to create 2 grand totals, one each for R and E by adding another filter
CALCULATE(sum,ALL(Table[country]),Table[R/E]=“R”)