TOPN DAX without filter

[PijanMac]

I have an issue with the TOPN method. 

I have a table with a few columns with different Water parameters, they were filled in on different days  (so they have gaps) "InsertDate" column shows these dates. it is also based on different tanks.

Tank	InsertDate	HardnessF	Alkalinity	ph	Conductivity	Temperature
111	13-03-24 11:14	25.54		6.88	808	15
111	12-03-24 9:27	26.07		6.94	807.2	15.3
111	11-03-24 10:42	24.29	2.96	6.97	803.5	15.6
112	11-03-24 10:40	24.29	2.96	6.97	803.5	15.6
112	11-03-24 10:34	22.9	1.73	7.05	511.9	11.5
112	11-03-24 10:30	26.3	2.97	6.94	831.9	15.4
112	11-03-24 7:15				7.772	11.9

I made a Line charts which show me a trends of value for every parameter based on Insert date. and two slicers for user: "InserDate", and "Tank" to select each tank.

ISUEE: I would like to measure the +/- 3sigma value for each parameter based on only last 10 samples, independently/without the "InserDate" slicer but I can't handle how to remove a filter from this "InserDate" column only for TOPN method.

Here is my code.
+3sigma_HardnessF_last10samples =

VAR lastxsamples =

    TOPN (10, WaterData,'WaterData'[HardnessF],DESC)

RETURN

    IF(SELECTEDVALUE(WaterData[Location])="Poland",

    CALCULATE(

    AVERAGE('WaterData'[HardnessF])+(3*STDEV.P('WaterData'[HardnessF])),lastxsamples),"")

I will be grateful for any help.

[Anonymous]

Hi  @PijanMac ,

You can try the following DAX:

Rank =
RANKX(    FILTER(ALL('WaterData'),'WaterData'[Tank]=MAX('WaterData'[Tank])),CALCULATE(SUM('WaterData'[HardnessF])),,DESC)

+3sigma_HardnessF_last10samples =
IF(
    SELECTEDVALUE('WaterData'[Location])="Poland",
    CALCULATE(
        AVERAGE('WaterData'[HardnessF])+
        3*STDEVX.P(
            FILTER(ALL('WaterData'),
            [Rank]<=10),[HardnessF])))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.