topic Re: Hi All ,How to get the percentage calculated for each heading in a matrix visual. in DAX Commands and Tips

Hi All ,How to get the percentage calculated for each heading in a matrix visual.

remyacholayil — Tue, 05 Apr 2022 09:53:50 GMT

Hi All ,

We have a model build in power bi like this.Using this model i have to built a matric visual in power bi which has unit in the column and liquidity name,expense heading and expense header sub categories in the rows.The values should be sum of amount and GTN%.The challenging part is to create the Gross to Net percentage creation.The customer has asked us to show the GTN percentage for different expense heading as shown below. For example ,if the value for expense heading one is 2 and the total value for the liquidity type="income" is 900.The percentage has to calculated like 2/900 for each expense heading.Iam struggling to get this done.

Here the expense heading come from one dimension table called dim expense heading,liquidity comes from another table which has both income and expense liquidity, the unit comes from different dimensions.The fact has all the surrogate key for these dimensions.

I have created a DAX =calculate(sum(amount),dimliquidity="expense")/calculate(sum(amount),dimliquidity="INCOME").But this is not showing the percentage across all the expense heading rows.

	Unit1(dim unit)
	Amount	GTN%
liquidity(income)(dim-liquidity)	968

liquidity(expense)
expense1(dimexpense heaidng)	-28	0%
expense2	-45	-5%
expense3	-153	-16%
expense4	-12	-1%
expense5	-23	-2%
expense6	-14	-1%
expense7	-4	0%
expense8	-2	0%
expense9	-42	-4%
expense10	-40	-4%
expense11	0	0%
Total Expenditure	-364	-35%

This is showing only at the higher level.not at athe expense heading levels.

i want to populate percentage in all the rows aganist the expense heading name corresponding to that.

Could anyone help meplease

Re: Hi All ,How to get the percentage calculated for each heading in a matrix visual.

V-lianl-msft — Mon, 28 Mar 2022 05:54:09 GMT

Hi remyacholayil ,

Please paste the data in the correct format to let us know your scene better

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Hi All ,How to get the percentage calculated for each heading in a matrix visual.

PiEye — Mon, 28 Mar 2022 09:11:41 GMT

Hi Remy

In order to achieve this, you first need to calculate the totals per category / sub category

Then Divide by the category / subcat total to get the percent.

Subtotals can be worked out by using Calculate with an ALLEXCEPT() function. This will work out the totals & subtotals for the fields listed within.

IncOrExp Total = CALCULATE ( Sheet1[Total Amt], ALLEXCEPT(Sheet1,Sheet1[Inc or Exp])) Inc or Exp % = Sheet1[Total Amt]/[IncOrExp Total] Cat Total = CALCULATE ( Sheet1[Total Amt], ALLEXCEPT(Sheet1,Sheet1[Inc or Exp],Sheet1[Cat])) Cat % = Sheet1[Total Amt]/[Cat Total]

Hope this helps!

Pi Eye

Re: Hi All ,How to get the percentage calculated for each heading in a matrix visual.

remyacholayil — Tue, 05 Apr 2022 14:14:52 GMT

Hi PiEye V-lianl-msft @amitchandak

that didnt help me could you please suggest a solution.I have updated the description.