cancel
Showing results 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

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:

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
Community Support

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.
Continued Contributor

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

Community Support

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.
Continued Contributor

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

Super User

I doubt Avg might not give the desired result

Try

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

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.

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.

Super User

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors