Forum Discussion

emutuc's avatar
emutuc
Frequent Visitor
2 years ago
Solved

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"

 

Many thanks in advanced

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

  • kpost's avatar
    kpost
    Solution Sage
    2 years ago

    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.

  • emutuc's avatar
    emutuc
    Frequent Visitor
    2 years ago

    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's avatar
      kpost
      Solution Sage
      2 years ago

      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's avatar
        emutuc
        Frequent Visitor
        2 years ago

        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