Calculate weighed average

[Anonymous]

I want to make a measure that calculate a weighted average of how many minutes our customers have been actively shopping in our store but I can´t for the life of me figure out how to do this in DAX.

Say I have these two lines

minutesInStore	minutesQueue	Customers
30	5	10
20	10	5

I want to figure out how long time the average customer have spend in my store actively shopping (minutesInStore-minutesQueue)

My normal go-to in SQL would be to make a column calculating the total minutes per line (minutesInStore-minutesQueue)*Customers as customerMinutes)

minutesInStore	minutesQueue	Customers	customerMinutes
30	5	10	250
20	10	5	50

and then the sum of customerMinutes divided by customers (sum(customerMinutes)/sum(customers) as avgMinutes

(250+50)/(10+5)=20

But how can I replicate that in DAX?

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

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

[johnt75 · Accepted solution]

Try

Avg Minutes =
VAR CustomerMinutes =
    SUMX (
        'Table',
        ( 'Table'[Minutes in store] - 'Table'[Minutes In Queue] ) * 'Table'[Customers]
    )
VAR Customers =
    SUM ( 'Table'[Customers] )
RETURN
    DIVIDE ( CustomerMinutes, Customers )