How to count the measure

[pyh]

Hi,

I create a measure for calculating the average + stdev using data from previous month. tham I compared it to the data of current month to see how many data points that is greater than this measure.Here is the result:

**This is the formula of Measure and Out of Measure:

Measure = 
var avg_of_previous_month = 
    CALCULATE([Average],PREVIOUSMONTH('Calander'[Date]))
var std_of_previous_month = 
    CALCULATE([Stdevs],PREVIOUSMONTH('Calander'[Date]))
return avg_of_previous_month+std_of_previous_month

Out of Measure = 
if(sum('Daily Database'[Value])> [Measure],1,BLANK())

Than I want to make a summary table like below to show how many data points fails out the measure each month.

I tried some formula like this but it did not return the satisfied result...

Count = COUNTROWS(
    FILTER('Daily Database',[Out of Measure]=1))

[v-zhangti]

Hi, @pyh 

I simulated some data and hopefully fit your situation, and you mainly look at Dax's approach.

Count = 
Var _table=SUMMARIZE('Table','Table'[Month],'Table'[Product],"Sum",[Out of Measure])
Return
SUMX(_table,[Sum])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[pyh]

Hello @v-zhangti,

Thanks for the response. Really appreciate it!

However, it did not work on my end. I believe this is because "Out of Measure" is not a field value like the ones you created in the file. Despite this, I noticed that the data is still displayed in the visualization table.

I attached my file here. 

https://drive.google.com/file/d/1PIj5ZQU3PSJB1L2KrJqohiqWNA7RciVv/view?usp=sharing 

Hope it's more clear for my question.

Thank you for your time and assistance=)

[v-zhangti · Accepted solution]

Hi, @pyh 

You can try the following methods.
Measure:

Count = 
Var _table=ADDCOLUMNS(Calander,"Sum",[Out of Measure])
Return SUMX(_table,[Sum])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[pyh]

Hello @v-zhangti ,

Really appreciate your help! It works now!

If it's not too big of an explanation - May I know the reason for choosing this DAX is because it could work on Measure?

[v-zhangti]

Hi, @pyh 

A virtual table needs to be created to put month and [Out of Measure] into one table. This virtual table is written out separately as such.

At this point you want to do a count of [Out of Measure], which is really a sum of 1 for [Sum].

ADDCOLUMNS function (DAX) - DAX | Microsoft Learn

SUMMARIZE function (DAX) - DAX | Microsoft Learn

Best Regards