Power BI How to calculate average/stdev of slicer selected items within Date Range?

Question

I have a Power BI table looks like this:

Cat sales   Date
apple   2.0 03/18/2021
apple   1.8 03/19/2021
apple   2.0 03/20/2021
apple   2.5 03/21/2021
peach   3.1 03/18/2021
peach   3.0 03/19/2021
peach   1.7 03/20/2021
peach   2.0 03/21/2021
pear    4.2 03/18/2021
pear    4.4 03/19/2021
pear    3.9 03/20/2021
pear    4.9 03/21/2021

I want to use a date slicer and Cat filter to calculate the (average sales)/(standard deviation of sales) during a period and display in a tile:

For example: Slicer select: Apple and Pear Date Slicer: 03/18/2021 - 03/20/2021

Formula:

Step 1:   Average = (6.2 + 6.2 + 5.9) = 6.1
03/18/2021   2+4.2= 6.2
03/19/2021   1.8+4.4 = 6.2
03/20/2021   2+3.9 = 5.9

Step 2:   Stdev(6.2,6.2,5.9) = 0.1732

Step 3:   Average/Stdev = 6.1/0.1732 = 35.22

Tile display: 35.22

I am new to DAX and wasn't able to figure out the right formula, Please help!

Sorry, what is the question? If you have a measure showing in a card, and you have slicers that apply to the card, then the value showing in the card will be factoring in the slicers. So just create your measure(s) and use them in visuals, then apply slicers. — teylyn
Thanks! I was able to create separate Average or STDEV, but how can I make them divide? sorry i am new to power BI — William .Wu

William .Wu William .Wu · Accepted Answer · 2021-03-26T02:09:29

I figured it out myself (correct me if i am wrong) and posted here if it ever be useful for other people;

Ratio = 
AVERAGEX(
    KEEPFILTERS(VALUES('Data_table'[Sales])),
    CALCULATE(AVERAGE('Data_table'[Sales]))
)/ 
STDEVX.P(
    KEEPFILTERS(VALUES('Data_table'[Sales])),
    CALCULATE(SUM('Data_table'[Sales]))
)

Power BI How to calculate average/stdev of slicer selected items within Date Range?

1 Answers