Issues with average per category

[Anonymous]

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

There is a sample pbix: https://drive.google.com/open?id=1lljwNxY3PcgW72nM-v4ek8W1ds2zPjQu

Can anyone help me with this issue?

[Anonymous]

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

There is a sample pbix: https://drive.google.com/open?id=1lljwNxY3PcgW72nM-v4ek8W1ds2zPjQu

Can anyone help me with this issue?

[sturlaws · Accepted 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/

[Anonymous]

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