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
Anonymous
Not applicable

Sums of subtotals

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

ExcelCalcVsPBI.PNG

 

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

 
Unweighted: CALCULATE(
            (sum('FactAchieved'[Achieved]) /[TargetPeriod])
            *100)
 

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 (

    Unweighted *Weight calc;
sumx('Fact TargetDepartment';'Fact TargetDepartment'[SumWeightPrTeam])
)

 

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:

DataModel_v1.png

 

Hope someone out there could help me with this case, please! Would be much appreciated!

Need more info? Please ask.

 

Best regards

Torgeir, Norway

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Superb! Thank you very much! 

I was fiddling with the AVERAGEX... Close, but no cigar. But you helped me out 🙂 

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.