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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.