cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Measure for Constant Line where numerator is a Measure

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"

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Frequent Visitor

@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

Super User

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.

Frequent Visitor

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

Community Support

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.

Super User

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors