Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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",
IF(
[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 =
COUNTROWS(
FILTER(ALLEXCEPT('Verizon Call Reason','Verizon Call Reason'[call_reason_derived] ), CALCULATE([Alert] = "No Alert", DATEADD('Verizon Call Reason'[session_date],-1,DAY))
))
EDIT:
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.
This card is [Alert Sum]
Solved! Go to Solution.
@Anonymous - OK, I think something like:
Measure =
VAR __Date = MAXX(ALL('Table'),[Date])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Table'),[Date]=__Date),
[Call Reason],
"Measure",[Alert] //this is your alert measure
)
RETURN
COUNTROWS(FILTER(__Table,FIND("Y - ",[Measure],,0)>0)))
@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
I made an edit, please let me know if this helps
@Anonymous What else is in that table besides what you have shown? Date I assume, anything else? Like an ID or ? Is each row in that table a different item on the same day? Sorry, still unclear.
@Greg_Deckler, thank you for the help - I can try to be clearer
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?
@Anonymous - OK, I think something like:
Measure =
VAR __Date = MAXX(ALL('Table'),[Date])
VAR __Table =
SUMMARIZE(
FILTER(ALL('Table'),[Date]=__Date),
[Call Reason],
"Measure",[Alert] //this is your alert measure
)
RETURN
COUNTROWS(FILTER(__Table,FIND("Y - ",[Measure],,0)>0)))
This worked beautifully! Thank you!
@Anonymous Awesome! Glad to hear it! The right information goes a long way!! 🙂
@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |