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 of staff working in each country and for each date.

 

I am trying to create a weighted calculation for # sales / staff.

 

See the table below with dummy data:

 SalesDateMonth

# Raw Sales

Staff

Adjusted Sales

01/06/2019

4

2

2

01/07/2019

16

4

4

01/08/2019

21

4

5.25

01/09/2019

25

4

6.25

Total

66

 

25.55

 


It's quite easy to produce the above in DAX, the total is wrong - I tried:

Adjusted Sales = DIVIDE(COUNTROWS(Sales),AVERAGE(StaffWorking[Staff]))
 

Please can someone help me with the DAX.

 

The pbix model with dummy data is attached here:
https://mega.nz/#!rwNBTSSY!cK0EAKschH1tAcXspNRdF4rwCY9YoOXebyREhmXVamI

 

Thanks

Need Help

7 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi alexei7 

    Create a measure

    Measure = IF(ISINSCOPE(Sales[SalesDateMonth]),[Adjusted Sales],SUMX(ALLSELECTED(Sales),[Adjusted Sales]))

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • alexei7's avatar
      alexei7
      Continued Contributor
      6 years ago

      Hi Maggie,

      Thanks very much for your help.

      This does work in that specific visual, however I also want to use this metric in a matrix visual with the date.
      Equally I noticed that the metric only worked when the slicer is being used, i want this to work all the time.

       

      The below is the kind of chart where i want this metric to also work:

       

       

       

       

       

       

       

      Let me know if you have any questions where I can clarify.

       

      Thanks

      Alex

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        6 years ago

        Hi  alexei7 

        Do you want to column total of matrix to show correct?

        If so, create a measure

        Measure 2 =
IF (
    ISINSCOPE ( Sheet1[date] ),
    [Measure],
    SUMX (
        FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[country] = MAX ( Sheet1[country] ) ),
        [Measure]
    )
)

        Best Regards
        Maggie
        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    I doubt Avg might not give the desired result

    Try

     

    Weight = DIVIDE(sum(Sales[Sales Amount]),LASTNONBLANK(StaffWorking[Staff],1))

     

     

    • 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