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

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

categories.png

 

Time values

values.png

1 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

littlemojopuppy_0-1610065856889.png

Hope this helps!  🙂

 

 

View solution in original post

9 REPLIES 9
v-robertq-msft
Community Support
Community Support

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
Community Champion
Community Champion

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

Screenshot 2021-01-08 011140.png

 

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

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

Untitled.png

 

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

littlemojopuppy
Community Champion
Community Champion

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

Anonymous
Not applicable

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!

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.
Untitled.png

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@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?

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

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

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

littlemojopuppy_0-1610065856889.png

Hope this helps!  🙂

 

 

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.