Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mattio
Frequent Visitor

AverageX on a virtual table

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.

 

AverageXAverageX

 

 

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]
                    )*/
)

 

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors