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 weighte...
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!  ğŸ™‚

     

     

littlemojopuppy's avatar
littlemojopuppy
Community Champion
5 years ago

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

  • 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)

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        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!  ğŸ™‚

         

         

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      5 years ago

      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's avatar
        littlemojopuppy
        Community Champion
        5 years ago

        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?