cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PshemekFLK
Helper III
Helper III

SUMX weighted average doesn't work

Hi,

 

I can't figure out why sumx doesn't result in the correct weighted average:

 

PshemekFLK_0-1616508399501.png

Formula for Funnel Speed (Months) = 

IF( HASONEVALUE('Funnel Data'[Opportunity Closed Mo No]),
     SUM('Funnel Data'[Funnel Speed])/30,
             DIVIDE(SUMX('Funnel Data','Funnel Data'[Funnel Speed]/30*'Funnel Data'[Opportunities Won Value USD]),
                       SUM('Funnel Data'[Opportunities Won Value USD])))
 
The correct result for Total should be ((15,561*1.9)+(17,999)*0.4)+(47,335*3.9))/80,895 = 221,337/80,895 = 2.7 instead of 1.2
 
There are visual filters applied on that matrix. Is that the issue?
 

 

 

3 REPLIES 3
amitchandak
Super User
Super User

@PshemekFLK , try a measure like

 


Formula for Funnel Speed (Months) =
IF( Sumx(values('Funnel Data'[Opportunity Closed Mo No]),calculate(
SUM('Funnel Data'[Funnel Speed])/30,
DIVIDE(SUMX('Funnel Data','Funnel Data'[Funnel Speed]/30*'Funnel Data'[Opportunities Won Value USD]),
SUM('Funnel Data'[Opportunities Won Value USD]))))

@amitchandak 

PshemekFLK_0-1616519430559.png

When I try to fix the brackets in this formula I still get 1.2 as Total not 2.7.

 

I see you've changed the first part of the formula. I thought it's the second DIVIDE part that produces a wrong result.

 

 

Can anyone give it a try? I still can't get to the right result

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors