Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Hi @alexei7
Create a measure
Measure = IF(ISINSCOPE(Sales[SalesDateMonth]),[Adjusted Sales],SUMX(ALLSELECTED(Sales),[Adjusted Sales]))
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
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] ) )
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
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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |