Forum Discussion
Help with Running Totals...with some twists
Hi DK_16 ,
We can create four measures to meet your requirement after create two what-if parameter:
M Cool Temp High Faults = IF(SUM('Table'[Colling Temperature]) < [Yerrow Value], 0,IF(SUM('Table'[Colling Temperature]) <[Red Value],1,3))M Cool Temp High SO = SUMX(TOPN(3,FILTER(ALLSELECTED('Table'),'Table'[Substation SN] <= MIN('Table'[Substation SN])),[Substation SN],DESC),[M Cool Temp High Faults])RedNotify Count = COUNTROWS(FILTER(SUMMARIZE('Table','Table'[Substation SN],"M",[M Cool Temp High SO]),[M] >= 6))YellowNotify Count = COUNTROWS(FILTER(SUMMARIZE('Table','Table'[Substation SN],"M",[M Cool Temp High SO]),[M] >= 2 && [M] <6))If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.
Best regards,v-lid-msft , I believe I figured out where our difference comes from. I have a substation sn column that represents each system (Sub_317, Sub 329, etc). Each system has it's own index column based on the event timestamp for each data point (the 6772-6788 values). It looks like you may have been using the index as the substation sn value. I created a unique id column from merging the substation sn and index columns. I used that value in the SO measure and everything seems to work as I would like. Thank you again for your help!
M Cool Temp High SO = SUMX( TOPN( 3, FILTER( ALLSELECTED(SampleData), SampleData[SN_Index] <= MIN(SampleData[SN_Index])), [SN_Index],DESC), [M Cool Temp High Faults])
Not sure what happened to the rest of my post, but here it is....
Here is a screenshot of what I'm looking to generate:
I'd like to count the number of times the running total is 6 or higher, then again the number of times its >=2 and <6. Greater than 6 generates a red level notification, between 2 and 6 generates a yellow notification. So basically, if at least two cooling temp values are greater than the red threshold in three consecutive data points, a red noficication is generated. If at least twice the temp value is between the red and yellow threshold values, a yellow level notifcaiton is generated. What I'm really trying to get down to is how many of each notifications are generated.
v-lid-msftCommunity Support
Hi DK_16 ,
We can create four measures to meet your requirement after create two what-if parameter:
M Cool Temp High Faults = IF(SUM('Table'[Colling Temperature]) < [Yerrow Value], 0,IF(SUM('Table'[Colling Temperature]) <[Red Value],1,3))M Cool Temp High SO = SUMX(TOPN(3,FILTER(ALLSELECTED('Table'),'Table'[Substation SN] <= MIN('Table'[Substation SN])),[Substation SN],DESC),[M Cool Temp High Faults])RedNotify Count = COUNTROWS(FILTER(SUMMARIZE('Table','Table'[Substation SN],"M",[M Cool Temp High SO]),[M] >= 6))YellowNotify Count = COUNTROWS(FILTER(SUMMARIZE('Table','Table'[Substation SN],"M",[M Cool Temp High SO]),[M] >= 2 && [M] <6))If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.
Best regards,v-lid-msft , appreciate your help!
What you have in your screenshot is what I'm after. I created the measures and what if parameters that you have. When I put the measures in a matrix, the SO measure shows the summed Fault measure total for each row instead of the sum of the 3 data points.
The measures I created:
M Cool Temp High Faults = IF( SUM(SampleData[Cooling Temperature]) < [Yellow Value], 0, IF( SUM(SampleData[Cooling Temperature]) > [Red Value], 3, 1))M Cool Temp High SO = SUMX( TOPN( 3, FILTER( ALLSELECTED(SampleData), SampleData[Substation SN] <= MIN(SampleData[Substation SN])), [Substation SN],DESC), [M Cool Temp High Faults])v-lid-msft , I believe I figured out where our difference comes from. I have a substation sn column that represents each system (Sub_317, Sub 329, etc). Each system has it's own index column based on the event timestamp for each data point (the 6772-6788 values). It looks like you may have been using the index as the substation sn value. I created a unique id column from merging the substation sn and index columns. I used that value in the SO measure and everything seems to work as I would like. Thank you again for your help!
M Cool Temp High SO = SUMX( TOPN( 3, FILTER( ALLSELECTED(SampleData), SampleData[SN_Index] <= MIN(SampleData[SN_Index])), [SN_Index],DESC), [M Cool Temp High Faults])