Forum Discussion

alexei7's avatar
alexei7
Continued Contributor
6 years ago

Total Incorrect for weighted calculation - DAX help

Hi everyone,   I'm struggling with DAX and hoping someone here can help.   My data is a table with a row for each sale, split by date and country. I have another table which contains the number ...
Need Help
alexei7's avatar
alexei7
Continued Contributor
6 years ago

Hi amitchandak ,

 

Thanks for looking at this. Yes, my formula with average is incorrect.

 

I tried your code (adjusting a little because i am looking for a count of sales, not the income) and unfortunately the total is not right - see screenshot.

 

 

 

 

 

 

The correct total should be 17.5 (the sum of the monthly weighted values).

 

Formula I used: Weight = DIVIDE(COUNTROWS(Sales),LASTNONBLANK(StaffWorking[Staff],1))

 

I found similar posts on the forum (e.g. https://community.powerbi.com/t5/Desktop/Weighted-average-incorrect-total/m-p/360009#M162425 ) but can't seem to work this out.

 

Thanks again for looking at this.

amitchandak's avatar
amitchandak
Super User
6 years ago

ok, You wanted the sum of weighted Avg in GT. That is tricky. Try something like this. I have not teseted this.

 

 

Column = sumx(Sales,DIVIDE(COUNTROWS(Sales),LASTNONBLANK(StaffWorking[Staff],1)))

 

You can also use Avg