topic Re: How to get average per person from two measures in DAX Commands and Tips

How to get average per person from two measures

sjacksonlincs — Wed, 12 May 2021 11:29:06 GMT

Hi,

I am relatively new to Power BI but am struggling with something that seems should be fairly straight forward...

I have two imported tables, one for 'Calls' and the other for 'Cash'.

I want to calculate the average cash collection by user which should be the total cash divided by number of calls taken.

On my cash table i have the measure:

SumOfCash = CALCULATE(SUM(Cash[SumOfAmount £]))

On my calls table I have the measure:

SumOfCalls = CALCULATE(SUM(Telephony[Accepted]))

Now i want to create a new measure that takes the cash measure and divides it by the calls measure.

I have tried this with:

AvgCashPerCall = CALCULATE(SUM(Cash[SumOfAmount £])/SUM(Telephony[Accepted])) which gives be an overall average total (which is correct) but when i create a table and in the users, the average per user shows as a very low decimilised number.

Can someone advise me what i'm doing wrong please?

Thanks

Re: How to get average per person from two measures

Anonymous — Wed, 12 May 2021 11:48:56 GMT

Your measure should be written as:

[AvgCashPerCall] = divide( [SumOfCash], [SumOfCalls] )

since code duplication is a the worst thing that can happen in programming.

However, to tell you whether this calculation is correct for any slice of your data... well, that requires to know your data, the model and the business requirements. It's not possible to say if that's correct right now since what DAX calculates depends on the underlying tables and the relationships between them.

Re: How to get average per person from two measures

sjacksonlincs — Wed, 12 May 2021 12:36:29 GMT

Anonymous thank you.

I have re-written to your above suggestion but still not able to arrive at the expected individual averages.

As pictured, the £24.81 average is correct but the minute i add a table and try to do it by user, the averages for each user are substantially lower.