- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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.

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculate Sum over range (non date range)

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculate Sum over range (non date range)

09-23-2022
06:44 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-24-2022
03:48 PM

`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}

}

)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-26-2022
12:12 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-26-2022
12:19 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-26-2022
12:58 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-26-2022
01:06 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-28-2022
09:44 AM

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}
}
)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-28-2022
12:46 PM

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])

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-03-2022
09:36 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-03-2022
12:20 PM

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

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

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

107 | |

95 | |

74 | |

62 | |

54 |

Top Kudoed Authors

User | Count |
---|---|

141 | |

99 | |

94 | |

86 | |

63 |