Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Issues with average per category

 

Hi,

 

I'm struggling with calculation avg salary per category.

I've created two measures:

Salary, EUR = 
var admission = IFERROR(CALCULATE(SUM('PI Entires'[Amount]); FILTER('PI Entires';'PI Entires'[PI Type]=2));0)
var legibility = IFERROR(CALCULATE(SUM('PI Entires'[Amount]); FILTER('PI Entires';'PI Entires'[PI Type]=3));0)
var month = CALCULATE(DISTINCTCOUNT('Calendar'[Month]); FILTER('Calendar';'Calendar'[Tab_sk]=1);FILTER('PI Entires';'PI Entires'[Amount]>0&&[PI Type]=2))
RETURN
IFERROR((admission+legibility)/month;BLANK())
AVG Salary per employee = 
VAR empl_count = SUM('Time-card entries'[Time card hours])/SUMX(FILTER('Calendar';'Calendar'[Tab_sk]=1); [Sched_hours])
RETURN
IFERROR([Salary, EUR]/empl_count; BLANK())
And everything looks great till I need a comparison of how employee average salary differs from average in its position type.
I have tried to write a DAX as usual, but it doesn't work, I need that that employee who is in Manager position have AVG Position type - 1060.91, Worker - 642,24 and so on...
AVG Position type = CALCULATE([AVG Salary per employee];ALLEXCEPT(Position;Position[Position type]))
I have several versions, I get the wrong result:
  • because I miscalculated DAX
  • because I use multiple measures
  • because of relationships between tables
Can anyone help me with this issue?
Need Help
  • sturlaws's avatar
    sturlaws
    7 years ago

    I got the values to correspond with your desired value by using this

    AVG Position type =
IF (
    NOT ( ISBLANK ( [Salary. EUR] ) );
    CALCULATE (
        [AVG Salary per employee];
        ALL ( Employee );
        KEEPFILTERS ( Position )
    );
    BLANK ()
)

    But are you sure your model is correct? I have not gone into details of it, but for the 2 employees without a position type the salaries are 424,95 and 156,99, while the total for this blank position type 1353,69. You might have some issues with the bidirectional filters you have set up, from 'Appointments' there are two possible paths to 'PI Entires', and this is something that might cause some issues: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

2 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    7 years ago

    I got the values to correspond with your desired value by using this

    AVG Position type =
IF (
    NOT ( ISBLANK ( [Salary. EUR] ) );
    CALCULATE (
        [AVG Salary per employee];
        ALL ( Employee );
        KEEPFILTERS ( Position )
    );
    BLANK ()
)

    But are you sure your model is correct? I have not gone into details of it, but for the 2 employees without a position type the salaries are 424,95 and 156,99, while the total for this blank position type 1353,69. You might have some issues with the bidirectional filters you have set up, from 'Appointments' there are two possible paths to 'PI Entires', and this is something that might cause some issues: https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, sturlaws, it works greate!
      And thanks for the link, it was very useful!