Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm struggling with a "sales performance report". Sum(sales per day) vs goal pr year for given KPIs.
My calculations give correct results pr line (pr KPI), but the overall scores (unweighted and weighted sales performance) are wrong.
Below I have inserted a picture of the exported data from Power BI to Excel, and I added the correct calculations(sums) marked with green. The yellow ones are the incorrect values from my calculations in Power BI. (I can send you the file if you want, didn't find a "upload file function").
PS! Sample data
Target YTD is Target for the year divided by the year's number of days muliplied by days since new year.
The value 67,51% (in cell E13) is the unweighted score I want. Achieved / Target YTD. The value of 109,41% (marked yellow) comes from Totalsum(Achieved)/TotalSum(TargetYTD)= 696 841 213 / 636 903 113 * 100 = 109,41
As you see, the value in cell G13 is slightly higher due to the KPI "Disbursed loans" weight of 30 (marked blue). In this case 69,07 is what I want.
Weighted sales performance: DIVIDE (
If I could only sum the line values, it would give correct result...
One issue here might be volume figures and number of sales together, or?
Here is my data model:
Hope someone out there could help me with this case, please! Would be much appreciated!
Need more info? Please ask.
Best regards
Torgeir, Norway
Solved! Go to Solution.
hi @Anonymous
This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
For your case, for [Unweighted] measure, you could add a new measure that
New Unweighted =AVERAGEX(VALUES('Dim KPI' [KPIName]),[Unweighted])
or
New Unweighted =AVERAGEX(VALUES('Dim KPI' [KPIName]),[Unweighted]+0)
and for [Weighted sales performance] measure, you could add a new measure that
New Weighted sales performance =SUMX(VALUES('Dim KPI' [KPIName]),[Weighted sales performance])
If not your case, please share your sample pbix file. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Lin
hi @Anonymous
This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
For your case, for [Unweighted] measure, you could add a new measure that
New Unweighted =AVERAGEX(VALUES('Dim KPI' [KPIName]),[Unweighted])
or
New Unweighted =AVERAGEX(VALUES('Dim KPI' [KPIName]),[Unweighted]+0)
and for [Weighted sales performance] measure, you could add a new measure that
New Weighted sales performance =SUMX(VALUES('Dim KPI' [KPIName]),[Weighted sales performance])
If not your case, please share your sample pbix file. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Lin
Superb! Thank you very much!
I was fiddling with the AVERAGEX... Close, but no cigar. But you helped me out 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |