topic Re: Help Making an advanced standard deviation with filter based on a column in DAX Commands and Tips

Help Making an advanced standard deviation with filter based on a column

Anonymous — Sat, 30 May 2020 11:58:28 GMT

Hi everyone, i need help with this problem:

Look at this table:

[Sales]

And from that table i made this visual (The Standard Deviation value is 65.68)

The Standard Deviation that I need is not showing in this visual because is taking the values of [Average Sales Per day] and I need that takes the values like the table of the image #2 to take this Standard Deviation Result (114.39):

Thank You very much for reading.

Re: Help Making an advanced standard deviation with filter based on a column

mahoneypat — Sat, 30 May 2020 13:21:04 GMT

You need to use the STDEVX.P() function. Please try this approach

Std Complex = STDEVX.P(VALUES(Table[Seller Name]), CALCULATE(Average(Table[Avg Sales Per Day])))

Where Table is your table name and the column (not measure) is in the Average(). Having the Calculate is required (or you can just use your measure that is the average of that column instead).

If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Help Making an advanced standard deviation with filter based on a column

Anonymous — Sat, 30 May 2020 16:01:49 GMT

Thanks for the response, in tried your recommendation but gave me another value the problem still look:
The right value is 114.40 in this case.

Re: Help Making an advanced standard deviation with filter based on a column

stevedep — Sat, 30 May 2020 17:28:11 GMT

Can you share a Power BI file with a sample of your data so we can work with this?

Re: Help Making an advanced standard deviation with filter based on a column

Anonymous — Sat, 30 May 2020 17:46:39 GMT

Sure, clic here:

Power BI Data

Re: Help Making an advanced standard deviation with filter based on a column

stevedep — Sat, 30 May 2020 18:04:15 GMT

Thanks, that helps. See solution below:

stdevm = CALCULATE(STDEVX.P(VALUES(Sales[Seller Name]);CALCULATE(sum(Sales[Average Sales per Day]))))

as seen here:

See modified Power BI file here.

If this is what you are looking for, please mark as solution. Kudo's appreciated.

Kind regards,

Steve.

Re: Help Making an advanced standard deviation with filter based on a column

Anonymous — Sat, 30 May 2020 18:13:53 GMT

Excelent!

Thank you both.