topic DAX weighted average help for Power pivot in DAX Commands and Tips

DAX weighted average help for Power pivot

Tinkelbell — Thu, 25 May 2023 13:21:52 GMT

		Rm Group	Values
		Beans		nuts		Flour
Date (Month)	Document	Sum of Outstanding Qty	Weight2	Sum of Outstanding Qty	Weight2	Sum of Outstanding Qty	Weight2
Apr	SG	1000000	1.068.350	1000000	3000000	700000	1700000
	PN	68350	1.068.350	1000000	3000000	700000	1700000
	Dxb			1000000	3000000	300000	1700000
Apr Total		1068350		3000000		1700000
May	SG	1000000	2.000.000
	PN	1000000	2.000.000
	Dxb
May Total		2000000
Jun	SG
	PN
Jun Total
Jul	SG
	PN
Jul Total
Oct	SG
	PN
Oct Total
Nov	SG
	PN
Nov Total
Dec	SG
	PN
Dec Total
Grand Total

I need help in creating a Dax formula for this pivot. I want the total outstanding qty for every month and group( Beans, nuts etc. to appear in the document rows ( just the yellow numbers.) How can I compute a DAX formula for that? Would really love to hear some suggestions as I have been struggling with this for days!

Re: DAX weighted average help for Power pivot

lbendlin — Sat, 27 May 2023 02:49:33 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

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

Re: DAX weighted average help for Power pivot

Anonymous — Tue, 30 May 2023 07:23:41 GMT

Hi Tinkelbell ,

I think your table should look like as below.

You can try code as below to create a measure.

Weight2 = CALCULATE(SUM('Table'[Outstanding Qty]),ALLEXCEPT('Table','Table'[Date(Month)],'Table'[Rm Group]))

Result is as below.

Best Regards,
Rico Zhou

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