Measure for Constant Line where numerator is a Measure

[emutuc]

Hello,

I need help with coming up with a measure that will be used as constant line to represent companywide average productivity versus an individual's productivity.  

The numerator is a "measure" I created and it includes the productivity count.

The denominator is from the fact table and includes the count of all users

The 'Measure' i'm thinking will look like this

1. Company Avg = CALCULATE( Average ('Table1'[COUNT COMBINED USE], ALL ('Table1'[USERS]))

• Table1 is the fact table 
• COUNT COMBINED USE is the Measure I created which includes productivity througout the day
• USERS is the list of company users.

Anything I'm doing wrong? I get the following error: "too many arguments were passed to the AVERAGE function. The maximum argument count for the function is 1"

Many thanks in advanced

[emutuc]

@kpost Thank you for the reply.  It's not working.  I notice that the [COUNT COMBINED USE] part isn't populating when i write the expression.  Maybe because it's a measure?

see below for measure expression and error message

[kpost]

Like I said, all I could promise is that my solution wouldn't "throw the (same) error", because I don't have insight into your data model.  And voila, it is now throwing a different error!  Success!  (sort of) 😂

If you re-create the problem with sample data in a .pbix file and upload it to google drive or catbox.moe or similar, then post the link, it is going to be much easier for me to help without having to ask a million questions back and forth 👍

What I can tell you is that measures can be referred to without the Table name in which they are found.  Power BI enforces measures having unique names, so you can just refer to it as [COUNT COMBINED USE], you don't need the stuff before it.  I'm not promising this will fix your problem, just something to note.

[emutuc]

Thanks.  when i create the expression

Company Avg = CALCULATE(Average([COUNT COMBINED USE]), ALL('Table1'[USERS]))

I get an error because the [count combined use] part is not found

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @kpost  provided, and i want to offer some more information for user to refer to.

hello @emutuc , you can try the following meausre.

Company Avg =
VAR a =
    SUMX ( ALLSELECTED ( 'Table1'[USERS] ), [COUNT COMBINED USE] )
VAR b =
    CALCULATE ( DISTINCTCOUNT ( 'Table1'[USERS] ), ALL ( 'Table1' ) )
RETURN
    DIVIDE ( a, b )

Best Regards!

Yolo Zhu

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

[kpost]

The part where you modify the filter context using ALL() should be an argument to CALCULATE, not an argument to AVERAGE.

So I cannot promise that this will do what you are intending since I can't see your data model, but I can promise you that this won't throw the (same) error:

Company Avg = CALCULATE(Average('Table1'[COUNT COMBINED USE]), ALL('Table1'[USERS]))

Note that a parenthesis has been added.