AverageX on a virtual table

mattio — Tue, 05 May 2020 11:29:20 GMT

Hello all,

I have spent a number of hour researching and not getting anywhere, if there is anybody that can help I would appreciate it!

I have a table of dates, which i am building a forecast measure for called 't'. I am trying to add the measure shown in grey on the picture to my table (unsuccesfully) because I cant seem to apply the correct filter to my virtual table.

Could anybody suggest code that would allow me to put the figures in grey into my visual using DAX / virtual table only.

The code for my workbook (sample data):

let Source = Table.FromList(List.Dates(Date.From(DateTime.FixedLocalNow()), 14, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.SelectColumns(Table.AddColumn(Source, "t", each List.Repeat({[Column1]}, Number.RoundUp(Number.RandomBetween(50, 100)))), {"t"}), #"Expanded t" = Table.ExpandListColumn(#"Added Custom", "t"), #"Changed Type" = Table.TransformColumnTypes(#"Expanded t",{{"t", type date}}) in #"Changed Type"

My dax calculated measure (Sx ? = where a i would like expected output to be returned:

Sx ? = //return the average of all "Sx Ix" by weekday //t[t] = a list of dates VAR _weekday = WEEKDAY(SELECTEDVALUE(t[t]), 2) //the weekday of each t[t] VAR _t_by_date = //a summary of t grouped by t[t] with extra calculated a column called Sx Ix SUMMARIZE( t, t[t], "weekday", WEEKDAY(t[t]), "Sx Ix", DIVIDE( COUNTROWS(t), //a count of all rows grouped by t[t] CALCULATE( COUNTROWS(t), ALL(t), t[t] + 3 >= EARLIER(t[t]) && t[t] - 3 <= EARLIER(t[t]) ) / 7 //a rolling average of 7 days ) //the Sx Ix calculated column ) RETURN "?" /*AVERAGEX( FILTER( SUMMARIZE('t by date', 't by date'[weekday], "Sx", AVERAGE('t by date'[Sx Ix])), 't by date'[weekday] = _Weekday ), [Sx] )*/

The dax calculated table with calculated column (expected output):

t by date = VAR _sx_ix = VAR _weekday = SELECTEDVALUE(t[weekday]) VAR _t_by_date = SUMMARIZE( t, t[t], "weekday", WEEKDAY(t[t]), "Sx Ix", DIVIDE( COUNTROWS(t), CALCULATE( COUNTROWS(t), ALL(t), t[t]+3 >= EARLIER(t[t]) && t[t]-3 <= EARLIER(t[t]) ) / 7 ) ) RETURN ADDCOLUMNS( _t_by_date, "Sx ?", "?" /*"want to add formulated column here" VAR _Weekday = WEEKDAY('t by date'[t]) RETURN AVERAGEX( FILTER( SUMMARIZE('t by date', 't by date'[weekday], "Sx", AVERAGE('t by date'[Sx Ix])), 't by date'[weekday] = _Weekday ), [Sx] )*/ )

Re: AverageX on a virtual table

Greg_Deckler — Tue, 05 May 2020 11:21:11 GMT

Sample data, expected output please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: AverageX on a virtual table

mattio — Sun, 10 May 2020 20:48:47 GMT

Hi, yes thanks for that i did post the M code which contains the sample data as well as expected output as seen in the DAX examples.

topic AverageX on a virtual table in DAX Commands and Tips

AverageX on a virtual table

Re: AverageX on a virtual table

Re: AverageX on a virtual table