Reply
Frequent Visitor
Posts: 10
Registered: ‎11-08-2018
Accepted Solution

DAX sum of square of weekly sales

[ Edited ]

Hi. 

I have a measure [Sales Quantity2] that is defined as the sum of a column Sales[Quantum].

Now I want to calculate the std.dev and mean of [Sales Quantity2] for a given period. Week by week. The mean is easy, just calculate [Sales Quantity2] divided by number of weeks. 

To calculate the std.dev I need to sum up quantum for an entire week, square it, and then sum over weeks. How to do this?Capture.PNG

I.e. need to make a measure that can sum up SQ3 in this table


Accepted Solutions
Highlighted
Super User
Posts: 859
Registered: ‎06-23-2016

Re: DAX sum of square of weekly sales

can you post sample anonymised data?

in general I'd go with STDEVX.P or STDEVX.S  & SUMMARIZE
https://docs.microsoft.com/en-gb/dax/stdevx-s-function-dax

Measure =
STDEVX.S (
    ADDCOLUMNS ( SUMMARIZE ( Table, Table[Week No] ), "SQ2", [Sales Quantity2] ),
    [SQ2]
)

you would need to adjust blue description and maybe replace STDEVX.S with STDEVX.P

View solution in original post


All Replies
Highlighted
Super User
Posts: 859
Registered: ‎06-23-2016

Re: DAX sum of square of weekly sales

can you post sample anonymised data?

in general I'd go with STDEVX.P or STDEVX.S  & SUMMARIZE
https://docs.microsoft.com/en-gb/dax/stdevx-s-function-dax

Measure =
STDEVX.S (
    ADDCOLUMNS ( SUMMARIZE ( Table, Table[Week No] ), "SQ2", [Sales Quantity2] ),
    [SQ2]
)

you would need to adjust blue description and maybe replace STDEVX.S with STDEVX.P

Frequent Visitor
Posts: 10
Registered: ‎11-08-2018

Re: DAX sum of square of weekly sales

Worked great. Thanks Smiley Happy