Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
dadorsey
Frequent Visitor

Calculate a minimum value across multiple rows based on multiple fields

Hi,

I'm new to Power BI and struggling to figure out how to calculate a column.

 

My data has rows of unique transactions representing different problem transactions identified on an account. Each row shows the amount of the individual problem transaction and the year/month in which the problem transaction occurred. It also contains a running monthly total for that problem type and account. What I'm trying to do is calculate the last column shown in the sample below--identifying the earliest Year and Month for which the Monthly Running Total equals or exceeds 1,000 for a given problem type and account. If the running total for that problem type and account never equals or exceeds 1,000 I need no value returned for that column.

 

Based on my reading it seems like I should be able to use the CALCULATE function to find the minimum Year and Month in which the running total first hit or exceeded 1,000 for a given problem type and account, but so far my attempts to do so have all failed. 

 

Any suggestions would be greatly appreciated.

Thanks!

 

Problem Type

Account

Transaction ID

Transaction Year and Month

Amount

Monthly Running Total

Min Year and Month Running Total >=1K

A

1

001

201901

500

500

201903

A

1

002

201903

600

1100

201903

A

2

003

201901

800

800

 

B

1

004

201901

500

1000

201901

B

1

005

201901

500

1000

201901

B

2

006

201902

1200

1200

201902

 

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

You should be able to use a calculation like the following:

Measure = 
var _table = CALCULATETABLE(
Table1,
ALL(Table1),
VALUES(Table1[Problem Type]),
VALUES(Table1[Account]),
Table1[Monthly Running Total] >= 1000) RETURN MINX(_table, Table1[Transaction Year and Month])


Firstly I'm creating the _table varialbe using the ALL function to remove all filters from the table, then I add back just the Account and Problem Type filters as well as a filter for where the running total is >= 1000

 

Then I using MINX to find the minimum month from this variable

View solution in original post

7 REPLIES 7
d_gosbell
Super User
Super User

You should be able to use a calculation like the following:

Measure = 
var _table = CALCULATETABLE(
Table1,
ALL(Table1),
VALUES(Table1[Problem Type]),
VALUES(Table1[Account]),
Table1[Monthly Running Total] >= 1000) RETURN MINX(_table, Table1[Transaction Year and Month])


Firstly I'm creating the _table varialbe using the ALL function to remove all filters from the table, then I add back just the Account and Problem Type filters as well as a filter for where the running total is >= 1000

 

Then I using MINX to find the minimum month from this variable

Brilliant! Thanks so much! That works.

 

If I wanted to use that new calculated measure column in a slicer is there a way to do that? It seems like that may be not allowed with a calculated measure as when I try to pull it into one Power BI won't accept it. Am I just overlooking something simple there?

 

Thanks again, really appreciate the help.


@dadorsey wrote:

If I wanted to use that new calculated measure column in a slicer is there a way to do that? It seems like that may be not allowed with a calculated measure as when I try to pull it into one Power BI won't accept it. Am I just overlooking something simple there?

No, if you want to use something in a slicer you would need to use a column not a measure. If you wanted to do the same thing in a calculate column I would change the expression slightly. It's usually better to avoid using CALCULATE or CALCULATETABLE in column expressions if you can.

 

I would do something like the following for a column calc.

Column = 
var _currentAccount = Table1[Account]
var _currentProblemType = Table1[Problem Type]
var _table = FILTER( ALL(Table1),Table1[Problem Type] = _currentProblemType && Table1[Account] = _currentAccount && Table1[Monthly Running Total] >= 1000)
var _result = MINX(_table, Table1[Transaction Year and Month])
return _result

This column calculation is working exactly as expected but I ran across an unexpected wrinkle in my data as I was implementing it. Occasionally the data includes negative amounts that drive the running total back down below $1,000. In those instances the column is reporting the earliest year and month in which it hit the 1K threshold but I'd like it to return no value in this new column if the most recent year and month for that problem type and account is no longer 1K or over. Similarly if it hits the 1K threshold, later dips down below it, then later rises above it (like problem type C below) I'd like it to give me the earliest year and month in which it most recently hit the 1K threshold. 

 

This seems a lot more complicated than the problem I thought I was trying to solve. Any thoughts on how I might be able to incorporate that more complicated check?

Thanks!

Damon

 

Problem Type

Account

Transaction ID

Transaction Year and Month

Amount

Monthly Running Total

Min Year and Month Running Total >=1K

A

1

001

201901

500

500

 

A

1

002

201902

600

1100

 

A

1

003

201903

-400

700

 

B

2

004

201901

1000

1000

 

B

2

005

201902

-100

900

 

C

3

006

201901

1100

1100

201903

C

3

007

201902

-200

900

201903

C

3

007

201903

200

1100

201903

I think the following alteration to the calculated column expression should meet those new requirements. I've renamed some of the variables to make it easier to see what values they store. Basically I added an extra step to get the max date where the value was below 1000 then I only get the min date where it was after that date and greater than 1000. So the value should be able to go above and below 1000 multiple times and we will still only get the last date where it first went above 1000

 

Column = 
var _currentAccount = Table1[Account]
var _currentProblemType = Table1[Problem Type]
var _datesBelow1000 = FILTER( ALL(Table1),Table1[Problem Type] = _currentProblemType && Table1[Account] = _currentAccount && Table1[Monthly Running Total] < 1000)
var _maxDateBelow1000 = MAXX(_datesBelow1000, Table1[Transaction Year and Month])
var _datesAbove1000 = FILTER( ALL(Table1),
							Table1[Problem Type] = _currentProblemType 
							&& Table1[Account] = _currentAccount 
							&& Table1[Monthly Running Total] >= 1000 
							&& Table1[Transaction Year and Month] > _maxDateBelow1000)
var _lastMinDateAbove1000 = MINX(_datesAbove1000, Table1[Transaction Year and Month])
return _lastMinDateAbove1000

 

 

Yes, that's working. Thanks again for the help and for explaining so clearly how you got there. 

Thanks again. That column calculation works as expected too. Between those two examples I can see a lot of what I was doing wrong when I was trying to get the calculation on my own. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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