How to count the frequency output of a measure by date
I have a measure which evaluates whether a threshold is surpassed or not, and by how many units.
Alert = IF(
[Volume_C] > [YMax], "Y - Above 2 Standard Deviation",
[Volume_C] < [YMin], "Y - Below 1 Standard Deviation", "No Alert"
I am trying to sum up the number of rows I get on this date that say "Y - ..." whether it is above or below a certain standard deviation - but I end up counting the entire rows of the data set ~15,000 rather than just 1
Alert Sum =
FILTER(ALLEXCEPT('Verizon Call Reason','Verizon Call Reason'[call_reason_derived] ), CALCULATE([Alert] = "No Alert", DATEADD('Verizon Call Reason'[session_date],-1,DAY))
How can I count the rows from the measure 'Alert' conditionally as "Y - above stdev" or "Y - Below stdev" for the most previous day and push it to a card? I am taking the standard deviation of past 60 days for a list of categories, if a certain volume of each category goes over two standard deviations, the [Alert] measure will indicate "Y - above stdev'. If below stdev, [Alert] will indicate 'Y - below stdev'. If between the two stdev, [Alert] = 'No Alert'. I want to take a sum of the category counts for whenever [Alert] measure indicats some form of 'Y' and push the total number of [Alert]='Y' to a card for the previous day.
In this case, there is only 1 category indicated as 'Y - Below 1 stdev', and the card should say 1. But instead takes a sum of the entire number of alerts that say yes for the past 60 days.
So I have a 30 std dev calculation for each category - in the call reason. If the Value of each call reason category surpasses a stdev, above or below, ['Alert'] = 'Y- Below 1 Stdev' or ['Alert'] = 'Y-Above 1 Stdev'. If the value of a category in the column DOES NOT surpass a std dev on a particular day, the ['Alert'] = 'No Alert'.
I want to go through the most recent day (Now 9/14, earlier post is 9/13) and count the number of times there is ['Alert'] = 'Y...' Whether it is below or above stdev, and push that value to a card.
In this case the card would say '28' since every category triggered [Alert] = 'Y..'. Yesterday the card would have said '1' (from original post)
My problem is how can I get this count sum from the most recent day?