topic Re: Measure for Constant Line where numerator is a Measure in DAX Commands and Tips

Measure for Constant Line where numerator is a Measure

emutuc — Wed, 12 Jun 2024 22:58:43 GMT

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

Re: Measure for Constant Line where numerator is a Measure

kpost — Wed, 12 Jun 2024 23:36:50 GMT

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.

Re: Measure for Constant Line where numerator is a Measure

emutuc — Wed, 12 Jun 2024 23:53:53 GMT

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

Re: Measure for Constant Line where numerator is a Measure

kpost — Thu, 13 Jun 2024 00:02:52 GMT

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.

Re: Measure for Constant Line where numerator is a Measure

emutuc — Thu, 13 Jun 2024 01:18:14 GMT

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

Re: Measure for Constant Line where numerator is a Measure

Anonymous — Thu, 13 Jun 2024 06:33:48 GMT

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.