Averagex over multiple tables using Var function

[feltder]

Good morning, 

I am a beginner in DAX coding and hoping someone could help me out. 

I’m calculating average people leaving the Organisation by multiple values in different tables.

When the calculation is in two different measures it works but when trying using VAR it doesn’t calculate the average correctly. I also need to “CrossJoin” to multiple tables to get the average by those variables (see below). I want to use VAR because I have many meausres and the loading of the data into the visuals is taking a long time so I’m trying to improve efficiency.

First question: Is crossjoin the correct function to use for "group by" by multiple tables or is there a simpler way?

Second question: What Am I doing wrong in the measure below because the average output is not correct unfortunately compared to if I were to break this measure into two different ones.

Any help on this would be so appreciated!!

1. AvgTest =

VAR CountOut =

if(CALCULATE(COUNT('Data - Outgoings'[SIN]))=0,0,CALCULATE(COUNT('Data - Outgoings'[SIN])))

Return

AVERAGEX(

CROSSJOIN(

VALUES(X_Fiscal_Year[Fiscal_Quarter]),

VALUES(X_Classfication[Classification]),

VALUES(X_Unit[Division]),

VALUES(X_EEGroup_Combination[EE_Combination]),

VALUES(X_Tenure[Tenure]),

VALUES(X_Job_Family[Job_Family]))

, CountOut

)

[v-kkf-msft]

Hi @feltder ,

Maybe you can try the measure.

AvgTest = 
AVERAGEX (
    SUMMARIZE (
        'Data - Outgoings',
        X_Fiscal_Year[Fiscal_Quarter],
        X_Classfication[Classification],
        X_Unit[Division],
        X_EEGroup_Combination[EE_Combination],
        X_Tenure[Tenure],
        X_Job_Family[Job_Family],
        "_Count",
            IF (
                CALCULATE ( COUNT ( 'Data - Outgoings'[SIN] ) ) = 0,
                0,
                CALCULATE ( COUNT ( 'Data - Outgoings'[SIN] ) )
            )
    ),
    [_Count]
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NickolajJessen]

Your first question is difficult to answer without seeing you data, data model and desired output.

For you seconds question: Try wrapping your variables in a CALCULATE and see if it makes a difference:

VAR CountOut =
Calculate(
if(CALCULATE(COUNT('Data - Outgoings'[SIN]))=0,0,CALCULATE(COUNT('Data - Outgoings'[SIN])))
)

[feltder]

Hi Nickolaj,

Thank you for responding, unfortunately adding Calculate before did not work. I really don't understand why it doesn't work. When i break the measures into two different measures, it works.

Basically my crossjoin tables are my slicer tables and I have a two other tables which our employees leaving and coming into the organisation which are linked together with the slicer tables in the relationships. I'm trying to create some projections on how many hires the organisation will need based on a historical five year average. After revewing the numbers, everything I did works but it's just very slow and not efficient.

Slicer tables:

HR data set which connects to my slicer tables:

First part is that i'm trying to calculate average of incomings and outgoings by quarter, by classification, by division, by job number and by employment equity group. I am a beginner at this and I'm guessing there is a better way to do this, but I'm having trouble figuring it out. I found the CrossJoin function, but not sure if that is the best way to go. 

Thanks again for your help.

[NickolajJessen]

I'm having a hard time seeing the need for crossjoin in your case. 
Can you share what kind of visualization you are planning to use and how you expect it to look?

[feltder]

My visuals look like this. The measures are Projected Anticipatory, Projected Incoming and Outgoing and candidates needed. 

Basically, when calculating the averages of outgoings or incomings, I need to make sure it's by quarter, by classification, by division, by job number and by employment equity group so i'm able to slice the data by it after. Here is an example of an output data including all of the group by's: 

The ouput data you are seeing is outgoings based on a five year average by all of those groups. 

What i'm doing works but i think it's not the best way of doing it. I need it to be faster and more efficient and I think the cross joins are slowing things down, but I could be mistaken.

Thanks again for your help, I know it's probably not easy understanding what i'm doing lol.