Forum Discussion
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 |
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 >= 1000Then I using MINX to find the minimum month from this variable
7 Replies
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 >= 1000Then 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
