cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

Calculate Sum over range (non date range)

Hello everyone, I think I am close but I am getting tangled up in writing the filter to calculate the sum.
Here is a picture of the test data.   I am trying to calculate a sum of total KW (brown line) used when two themocopules(Tc1,Tc2) are above a given value( 2 degrees, blue lines) .
I have a calculated column that identifies when Tc1 and Tc2 are above the 2 degree temp.

Above2 = IF([TC1]>=2 && [TC2]>=2, "Yes","No")

So I started with Total=calculate(Sum[KW],[Above2]="Yes" ) this works, but it sums the KW value for the whole time the TC values are above 2 degrees.

What I need to find is total KW when the heater has reached steady state and the KW line has flattend out. Most of the data is no where this clean and flat, so start the summation after the Max value of KW has occurred and stop after the TC values have fallen below 2 degrees would be accecptiable.   Any suggestions on how to filter the data to obtain this total?

Here is the dax code snippet I used to make the example data table.

``````Example Data =
DATATABLE(
"Index", STRING,
"TC1", STRING,
"TC2", STRING,
"KW", STRING,
{
{01, 0, 0, 0},
{02, 5, 4, 2},
{03,7,6,3},
{04,9,9,5},
{05,10,10,4},
{06,10,10,4},
{07,10,10,4},
{08,7,8,0},
{09,5,6,0},
{10,0,0,0}
}
)``````

9 REPLIES 9
Super User

``start the summation after the Max value of KW has occurred and stop after the TC values have fallen below 2 degrees``

What if there are multiple maxima with the same value and the value dips below 2 in between?

``````Above2 and past Max =
var mx = maxx(all('Example Data'),[KW])
var mi = calculate (min('Example Data'[Index]),all('Example Data'),'Example Data'[KW]=mx)
return IF([TC1]>=2 && [TC2]>=2 && [Index]>=mi, "Yes","No")``````

By the way your datatable looked slightly off.

Example Data =
DATATABLE(
"Index", INTEGER,
"TC1", DOUBLE,
"TC2", DOUBLE,
"KW", DOUBLE,
{
{01, 0, 0, 0},
{02, 5, 4, 2},
{03,7,6,3},
{04,9,9,5},
{05,10,10,4},
{06,10,10,4},
{07,10,10,4},
{08,7,8,0},
{09,5,6,0},
{10,0,0,0}
}
)
Frequent Visitor

This looks very promising.   To add a wrinkle to the problem, what if my data table is composed of multiple heating cycles.  The insturment cuts on TC1 and TC2 heat up max KW is reached and repeat.

Will the way you have set up the variables find each Maximum KW or will it find the overall max KW?

Thanks, for catching the error on setting up my table.

Super User

One of my (unwritten) rules is there is no re-asking.  That means you cannot ask me the same question that I had asked you.

It is your responsibility to describe the rules on how to handle multiple maxima. Once you have done that I will be able to comment.

Frequent Visitor

The way I see it you could have a few different Maximum Conditions.
If we call each heat up and cool down a "Cycle"
1. Maximum for the whole data table, across all the "Cycles".   <-- I do not want this.
2. Multiple Maximus per Cycle.  <-- Here I would want the First Max that has occurs.  (They will change thier mind the moment I hit submit, but lets stick with this for now)

Thanks

Super User

Ah, the memories (signal theory was fun at school).  Please provide sample data that

- has more than one maximum

- and where the first maximum is lower than the max maximum

Frequent Visitor

One sample data set provided.   Thanks!!

``````Example Data =
DATATABLE(
"Cycle", STRING,
"Index", INTEGER,
"TC1", DOUBLE,
"TC2", DOUBLE,
"KW", DOUBLE,
{
{"A",01, 0, 0, 0},
{"A",02, 5, 4, 2},
{"A",03,7,6,3},
{"A",04,9,9,5},
{"A",05,10,10,4},
{"A",06,10,10,4},
{"A",07,10,10,4},
{"A",08,10,10,4},
{"A",09,10,10,4.875},
{"A",10,10,10,4},
{"A",11,10,10,4},
{"A",12,7,8,0},
{"A",13,5,6,0},
{"A",14,0,0,0},
{"B",01,0,0,0},
{"B",02,2,4,2},
{"B",03,4,5,3},
{"B",04,8,7,4},
{"B",05,10,10,5.5},
{"B",06,10,10,5},
{"B",07,10,10,5},
{"B",08,10,10,6},
{"B",09,10,10,5},
{"B",10,10,10,5},
{"B",11,10,10,5},
{"B",12,8,7,0},
{"B",13,6,5,0},
{"B",14,2,3,0},
{"B",15,0,0,0},
{"C",01,0,0,0},
{"C",02,3,2,2},
{"C",03,5,4,3},
{"C",04,10,10,4},
{"C",05,10,10,5},
{"C",06,10,10,4},
{"C",07,10,10,3},
{"C",08,9,8,0},
{"C",09,5,3,0},
{"C",10,2,1,0},
{"C",11,0,0,0}
}
)``````

Super User

Here's my new version

``````Above2 and past Max =
var a = summarize(ALLSELECTED('Example Data'),'Example Data'[Index],"k",sum('Example Data'[KW]))
var b = ADDCOLUMNS(a,"p",var i = [Index] return CALCULATE(sum('Example Data'[KW]),ALLSELECTED(),'Example Data'[Index]=i-1),"n",var i = [Index] return CALCULATE(sum('Example Data'[KW]),ALLSELECTED(),'Example Data'[Index]=i+1))
var firstmax = MINX(filter(b,[k]>[p] && [k]>[n]),[Index])
return IF(max('Example Data'[TC1])>=2 && max('Example Data'[TC2])>=2 && max('Example Data'[Index])>=firstmax, "Yes","No")``````

It will fail if the local maximum is followed by the exact same kW value for the next index - but you can fix that in the filter if you want.

filter(b,[k]>=[p] && [k]>=[n])
Frequent Visitor

Inserting the above equation as a measure, and trying to do a second measure to sum up the range of data marked as "Yes" by the above equation.

Trying it as a custom column it always equals "Yes" for the data set shown above.

Looking at the code, I think I understand this correctly, when you specify "var b" you are building a table not a single variable.  So running it as a custom column itsn't correct.

Super User

A calculated column can be used if the result is not impacted by filter choices.  Otherwise you must use a measure.

yes, variable b is a table variable. You can use these inside a calculated column or a measure as long as the final result is a scalar value  (the "return"  part)

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors