topic Re: Percentage over subtotal in hierarchical matrix in DAX Commands and Tips

Percentage over subtotal in hierarchical matrix

czaldumbide — Thu, 25 Jun 2020 18:43:23 GMT

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%

Re: Percentage over subtotal in hierarchical matrix

lbendlin — Thu, 25 Jun 2020 20:57:01 GMT

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

Re: Percentage over subtotal in hierarchical matrix

amitchandak — Fri, 26 Jun 2020 02:53:25 GMT

czaldumbide ,

see if something like this

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

Re: Percentage over subtotal in hierarchical matrix

czaldumbide — Fri, 26 Jun 2020 13:29:36 GMT

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]))))

Re: Percentage over subtotal in hierarchical matrix

czaldumbide — Mon, 29 Jun 2020 04:05:49 GMT

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

Thanks!

Re: Percentage over subtotal in hierarchical matrix

Anonymous — Mon, 29 Jun 2020 11:06:59 GMT

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