Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Weighted Average Calculation

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

Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    5 years ago

    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!  đź™‚

     

     

9 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, littlemojopuppy, thanks for replying.  

       

      ProductTime
      PROD143,8
      PROD138,9
      PROD140,4
      PROD130,2
      PROD238,3
      PROD228,7
      PROD236,3
      PROD327,5
      PROD328,4
      PROD436,6
      PROD437,9
      PROD539,8
      PROD640,5
      PROD636,7
      PROD737,3
      PROD723,8
      PROD837,2
      PROD932,5
      PROD932,1
      PROD943,9

       

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

       

      Thanks again!

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        5 years ago

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

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

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.