Average top level of matrix, sum for lower levels

[4778]

Hi, 

I need to show an average at the top level of a matrix and a sum for the lower 2 levels. 

The values is a measure and is:

Planned Loading % =

IF(

    HASONEVALUE(Sheet1[PROJECT_ID]),

    SUMX('Sheet1',(Sheet1[PLANNED_HOURS])/(Sheet1[Working Hours Per Week Per Employee])),  -- Actual values for lowest level

    IF(

        HASONEVALUE(Sheet1[RESOURCE_ID]),

        SUMX(Sheet1,(Sheet1[PLANNED_HOURS])/(Sheet1[Working Hours Per Week Per Employee])),  -- Summed values for middle level

        AVERAGEX(Sheet1,(Sheet1[PLANNED_HOURS])/(Sheet1[Working Hours Per Week Per Employee]))  -- Average for top level, Average should be each person's % divided by count of persons not average of each row

    )

)

The 1st and 2nd part works, it shows sums.

The 3rd part does not, 

Expected state is:

but I get:

https://drive.google.com/file/d/1msrpR6l5mK8zXqU-F_JyM5dHQ4JsK0Rp/view?usp=drive_link

[Fowmy · Accepted solution]

@4778 

Use the following modified mesaure: 

Planned Loading % = 

SWITCH(
    TRUE(),
    HASONEVALUE(Sheet1[PROJECT_ID]), SUMX('Sheet1',(Sheet1[PLANNED_HOURS])/(Sheet1[Working Hours Per Week Per Employee])),  
    HASONEVALUE(Sheet1[RESOURCE_ID]), SUMX(Sheet1,(Sheet1[PLANNED_HOURS])/(Sheet1[Working Hours Per Week Per Employee])),  
        AVERAGEX(
            VALUES( Sheet1[RESOURCE_ID] ),
            CALCULATE(SUMX(Sheet1,(Sheet1[PLANNED_HOURS])/(Sheet1[Working Hours Per Week Per Employee]))))
    )

[4778]

Awsome, this seems to  be working as required - thanks! 

[Fowmy]

@4778 

You can save your Power BI file in OneDrive, Google Drive or any other cloud storage and share the link here.

[4778]

Thanks @Fowmy :
https://drive.google.com/file/d/1msrpR6l5mK8zXqU-F_JyM5dHQ4JsK0Rp/view?usp=drive_link

[Fowmy]

Please grant access