Help needed with nested Iterator (STDEVX.P of a SUMMARIZED AVERAGEX)

[vpatel55]

I would like to produce the standard deviation of a virtual table produced by SUMMARIZE and AVERAGEX.

My AVERAGEX works fine. It sums up the daily sales for each weekday for the whole month, and then averages each weekday:

Average daily sales = 
AVERAGEX(
    SUMMARIZE (
        Sales,
        Sales[Date],
        Sales[Weekday],
        "Daily average", SUM (Sales[Units sold] )
    ),
    [Daily average]
)

 Here is the output:

I now need to write a DAX measure that calculates the standard deviation of this. I know that the function to use is STDEVX.P, but I don't know how to nest this. The answer should be 3.08, as it has been calculated in Excel:

Any help would be most appreciated on how I can create a measure that does this.  I have added the sample file here:

https://www.dropbox.com/s/6fl1wrzpqpe6wgi/Sample%20file.pbix?dl=0

[MattAllington · Accepted solution]

Try this (courtesy of Mat Herring)

This seems to work
Measure =
STDEVX.P (
VALUES ( Sales[Weekday] ),
VAR TotalSales =
CALCULATE ( SUM ( Sales[Units sold] ) )
VAR Days =
CALCULATE ( DISTINCTCOUNT ( Sales[Date] ) )
RETURN
DIVIDE ( TotalSales, Days )
)

[vpatel55]

Thanks @MattAllington and Mat Herring, I see you've thought about the problem in a different way and it works perfectly!

Big appreciation for this, and also your general work in the Power BI community.

[MattAllington]

No problem. It is very common for people, particularly people that come from a database background, to approach these problems by wanting to write a query. There are times when this is the right approach, but most often there is an easier way. The X functions are designed specifically to leverage lineage and table functions to do such calculations.