Trying to calculate weighted average by Year/Month in DAX

0
votes

I have a table with the following rows:

enter image description here

and I am trying to do a weighted average by Product and ReceiptYearMonth of CaseQuantity

I have created the following DAX measure to try to create the weighted average:

SumX Test = sumx(Query1, 
          Query1[CaseQuantity] / 
          calculate(
              sum(Query1[CaseQuantity]), ALL(Query1[RecadvLineId])
          )
       )

but that just returns the following:

enter image description here

i.e. the measure returns 4.00 for 201702 because there are 4 rows in the table for 201702. It returns 2.00 for 201703 because there are 2 rows for it. At this stage I think they should be returning 1.00 for each YearMonth.

Can anyone explain what I am doing wrong here ?

1
powerbidax

1 Answers

0
votes

I'm not certain I follow your weighting methodology, but is this what you're looking for?

SumX Test = SUM(Query1[CaseQuantity])/CALCULATE(SUMX(Query1,Query1[CaseQuantity]),ALL(Query1[RecadvLineId]))

It gives me this:

enter image description here

and this, if I add the RecadvLineId to the table:

enter image description here