Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
alexei7
Continued Contributor
Continued Contributor

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

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

Hi @alexei7 

Create a measure

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

Capture18.JPG

 

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.

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:

 

Capture.PNG

 

 

 

 

 

 

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

 

Thanks

Alex

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]
    )
)

Capture1.JPG

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.

Hi Maggie,

 

Thanks for your time in looking into this.

 

I don't quite understand your solution, and it doesn't correlate to my example, so I can't translate it, or make it work.

e.g. my example has two connected tables

 

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

amitchandak
Super User
Super User

I doubt Avg might not give the desired result

Try

 

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

 

 

alexei7
Continued Contributor
Continued Contributor

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.

screenshot.PNG

 

 

 

 

 

 

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.

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.