how to average directly from matrix

[andika]

Hi,

I tried to get the average of Minute of works from certain period per employee which measured the sum of MOW divide by (number working days - number public holiday and employee leave. Now i want my matrix show the average directly from the column without calculated average from the tabel again.

here is my matrix

I want the average total is shown the average of value from cell matrix. which is for example in 2023 jan i get the value = 526.1

i try the measurement below but it failed

Average MOW = if( ISINSCOPE(FactWorkDetails[PIC Full Name]),

    divide (SUM(FactWorkDetails[Minute of Works]) ,

    CALCULATE(

        COUNT('Date'[Dayinweek]),'Date'[Dayinweek]="weekdays")-

        CALCULATE(

            COUNTROWS(FILTER(Holiday,Holiday[PIC Full Name]=SELECTEDVALUE(FactWorkDetails[PIC Full Name])

            )

            )

            ,Holiday[Column]="weekdays")

            ),

    DIVIDE(DIVIDE (SUM(FactWorkDetails[Minute of Works]) , CALCULATE(COUNT('Date'[Dayinweek]),'Date'[Dayinweek]="weekdays")),DISTINCTCOUNT(FactWorkDetails[PIC Full Name])))

Really appreciate for the help. Thx

Here is my bix = https://drive.google.com/file/d/1AFtEbJo_CbiLRacKrCOFAg060goXxckr/view?usp=sharing

[andika]

HI @lbendlin really appreciate with your answer. 

ur answer so close with i wanted to find, but it cant handle the scenario :

1. FactWorkDetails[Date Our] can be on holiday date and / or Weekend date

2. Date in holiday table is belong to PIC Full name

but i follow your approach by creating new table and adjust the Dax formula to suit my need and it works wonders, but when i try to convert it into measure the grand total wont show average. can you help whats wrong with my measure (measure 4)

https://drive.google.com/file/d/1rRSBiPEYN6kQI4kYkvQV3Tz34YssqQzl/view?usp=sharing

[andika · Accepted solution]

Hi @lbendlin 

really appreciate your help with this issue, already got what i needed by combining your approach and my previous approach, the measure is like this :

Measure 4 = VAR a = SUMMARIZE(FactWorkDetails,FactWorkDetails[PIC Full Name],'Date'[Month])

var b = ADDCOLUMNS(a,"Working days",CALCULATE(

        COUNT('Date'[Dayinweek]),'Date'[Dayinweek]="weekdays")-

        CALCULATE(DISTINCTCOUNT ( Holiday[Date] ),FILTER(Holiday,Holiday[Column]="weekdays"),FILTER(Holiday,Holiday[PIC Full Name]=FactWorkDetails[PIC Full Name])),

        "MOW",CALCULATE(SUM(FactWorkDetails[Minute of Works])))

return AVERAGEX(b,DIVIDE([MOW],[Working days],0))

and the table matrix is like this, exactly the same when i do the manual calculation via excel

[lbendlin]

You can simplify your approach by "thinking like the grand total"