Weighted Average Calculation

[Anonymous]

Hey guys!

I'm trying to formulate an weighted average for a product data. I have 4k rows of differents IDs divided by 9 categories (prod1 to prod9), each one with a value of time.

I need an weighted average of the time using the number of IDs on each category as the weight.

Categories

Time values

[v-robertq-msft]

Hi, @Anonymous 

Has littlemojopuppy’s reply helped you to solve your problem?

Would you like to mark littlemojopuppy’s reply as a solution so that others can learn from it too?

Thanks in advance!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[CNENFRNL]

@Anonymous , I assume the logic of desired weighted average is like this

(total time of each product) * (occurrence of each product)/(total count of all products)

take Product1 for example:
(43.8+38.9+40.4+30.2)*(4/20)

Weighted Avg = 
VAR __t = COUNTROWS ( Table1 )
RETURN
    SUMX (
        DISTINCT ( Table1[Product] ),
        CALCULATE ( SUM ( Table1[Time] ) * COUNTROWS ( Table1 ) ) / __t
    )

btw, Excel array formula, our oldie but goodie, does the trick with ease,

=SUMPRODUCT(Table1[Time], COUNTIF(Table1[Product],Table1[Product]))/ROWS(Table1)

[littlemojopuppy]

@CNENFRNL  Didn't I offer a similar solution hours ago???

[littlemojopuppy]

Hi @Anonymous can you provide some sample data to work with?

[Anonymous]

Hi, @littlemojopuppy, thanks for replying.  

Product	Time
PROD1	43,8
PROD1	38,9
PROD1	40,4
PROD1	30,2
PROD2	38,3
PROD2	28,7
PROD2	36,3
PROD3	27,5
PROD3	28,4
PROD4	36,6
PROD4	37,9
PROD5	39,8
PROD6	40,5
PROD6	36,7
PROD7	37,3
PROD7	23,8
PROD8	37,2
PROD9	32,5
PROD9	32,1
PROD9	43,9

I hope it helps. Each category weight is the % of the ocorrences 

Thanks again!

[Ashish_Mathur]

Hi,

Try these measures:

Total time = SUM(Data[Time])

Product count = COUNTROWS(Data)

Measure 2 = SUMX(VALUES(Data[Product]),[Total time]*[Product count])/[Product count]

Hope this helps.

[littlemojopuppy]

@Ashish_Mathur  not only did I offer a solution five hours ago, someone else offered essentially the same solution three hours ago.  Your input was warranted?

[littlemojopuppy]

@Anonymous one more question: how is the weighing calculated?  I'm assuming 

(product total count) * (product time) / (total product count)

[littlemojopuppy · Accepted solution]

I'm working with my assumption and came up with this...

Weighted Average Product Time = 
    VAR AllProducts = 
        CALCULATETABLE(
            VALUES(Products[Product]),
            ALL(Products)
        )
    VAR	TotalProductCount =
	    CALCULATE(
		    [Product Count],
    		ALL(Products)
	    )
    RETURN 

    DIVIDE(
        SUMX(
            AllProducts,
            [Product Count] * [Total Product Time]
        ),
        TotalProductCount,
        BLANK()
    )

Here's the results

Hope this helps!  🙂