Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
AVG Position type = CALCULATE([AVG Salary per employee];ALLEXCEPT(Position;Position[Position type]))
Solved! Go to Solution.
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/
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/
Thanks, @sturlaws, it works greate!
And thanks for the link, it was very useful!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |