Percentage over subtotal in hierarchical matrix

[czaldumbide]

Hi Everyone,

I have the following hierarchical matrix with all my rows expanded. I want to calculate the % based on the respective total for In/Out of that account as you will see on the example below. 

As you can see for the first row I have an amount of 120, which if divided by Total In for that account will be 120/450 = 26.6%. The same goes with the subtotal for Type. They should also be calcualted based on the total for In/Out (ex: 370/450 = 82.2%)

In terms of the data, all fields in this matrix come from a single table called Transactions. 

Here is an example to guide you. My matrix obviously has several accounts, so I want the percentage calculated based on the totals for each account. 

Any suggestions on how to write a measure that could calculate what I want would be appreciated. Thank you

Name	Account	In/Out	Type	Counterparty	Country	Amount	Volume	%
A	123	In	Wires	Mario	USA	120	5	26.6%
-	-	-	Wires	Jason	JAPAN	250	10	55.5%
-	-	-	Total Wires	-	-	370	15	82.2%
-	-	-	Cash	Sarah	USA	45	2	10%
-	-	-	Cash	Mario	MEXICO	35	1	7.7%
-	-	-	Total Cash	-	-	80	3	17.7%
-	-	Total In	-	-	-	450	18	100%
-	-	Out	Wires	Kyle	CANADA	400	8	42.1%
-	-	-	Wires	Sarah	BOLIVIA	350	2	36.8%
-	-	-	Total Wires	-	-	750	10	78.9%
-	-	-	Checks	Camila	USA	200	5	21.1%
-	-	-	Total Checks	-	-	200	5	21.1%
-	-	Total Out	-	-	-	950	15	100%

[Anonymous]

Try this:

Amount % = 
var __currentAmount = [Total Amount]
var __inoutTotalAmount =
	CALCULATE(
		[Total Amount],
		KEEPFILTERS(		
			CROSSJOIN( 
				values( Transactions[Name] ),
				values( Transactions[Account] ), 
				values( Transactions[In/Out] )
			)
		),
		ALLSELECTED( Transactions )
	)
var __result =
	DIVIDE(
		__currentAmount,
		__inoutTotalAmount
	)
return
	__result

Best

D

[amitchandak]

@czaldumbide ,

see if something like this

divide([Amount], calculate([Amount],allexcept(Table,Table[Name],Table[Account],Table[In/Out]))

[czaldumbide]

Hi @amitchandak and @lbendlin 

Your suggestions are not giving me the correct numbers yet. The percentages are a lot smaller than they should so I'm assuming its not dividing bu the total In/out for that particular account. Let me know if you have other suggestions. 

My measure looks like this:

The part in red is not working proporly yet, but the part in blue which applies to all subtotals is. 

% over in/out = IF( ISFILTERED(TRANSACTIONS[Counterparty]),

DIVIDE(SUM(TRANSACTIONS[Amount]), CALCULATE(SUM(TRANSACTIONS[Amount]),

ALLEXCEPT(TRANSACTIONS, TRANSACTIONS[Name],TRANSACTIONS[Account], TRANSACTIONS[In/Out])), DIVIDE(SUM(TRANSACTIONS[Amount]), CALCULATE(SUM(TRANSACTIONS[Amount]), ALLSELECTED(TRANSACTIONS[Type]))))

[czaldumbide]

Any suggestions on how to do this? I still haven't figured out a solution by myself.

Thanks!

[lbendlin]

Use ALLEXCEPT to take [In/Out] and its parents out of the equation.