topic Re: how to average directly from matrix in DAX Commands and Tips

how to average directly from matrix

Anonymous — Fri, 12 Jan 2024 08:36:07 GMT

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

Re: how to average directly from matrix

lbendlin — Sun, 14 Jan 2024 20:16:54 GMT

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

Re: how to average directly from matrix

Anonymous — Mon, 15 Jan 2024 08:32:10 GMT

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

Re: how to average directly from matrix

Anonymous — Tue, 16 Jan 2024 01:35:27 GMT

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