Forum Discussion
Find the difference between values in a group
I'm new to Power BI and I am struggling to figure out how to calculate the difference between values within a group. I want to calculate the difference between the amount for the values within a group compared to the lowest value within the group.
| PROJECT | BID AMOUNT | COMPANY | RANK |
| Alpha | $113 | Mountaineers | 1 |
| Alpha | $119 | Wolfes | 2 |
| Alpha | $200 | Ram | 3 |
| Beta | $1,001 | Orders Co | 1 |
| Beta | $1,266 | J. Allen | 2 |
| Beta | $1,747 | Rock Forge | 3 |
| Beta | $2,803 | Angels, Inc | 4 |
| Gamma | $900 | Pavemaster | 1 |
| Gamma | $1,001 | Wonder Smash | 2 |
What I want to do is get the difference between each of the bid values and the value of the lowest bid, including the $0 difference for the lowest bid.
Hi JBHCSS ,
Try this:Dif = Var _project = MAX(Difference[PROJECT]) Var _bidAmount = MAX(Difference[BID AMOUNT]) Var _minBidAmount = CALCULATE(MIN(Difference[BID AMOUNT]),FILTER(all(Difference),Difference[PROJECT]=_project)) Var _diff = _bidAmount-_minBidAmount Return _diff
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
4 Replies
Hi JBHCSS ,
Try this:Dif = Var _project = MAX(Difference[PROJECT]) Var _bidAmount = MAX(Difference[BID AMOUNT]) Var _minBidAmount = CALCULATE(MIN(Difference[BID AMOUNT]),FILTER(all(Difference),Difference[PROJECT]=_project)) Var _diff = _bidAmount-_minBidAmount Return _diff
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielYour table will look like this:
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
NathanielI'm obviously doing something wrong because when I enter
MAX(Difference[PROJECT])or any of the other similar commands I get a "cannot find name" error for "Difference" and "PROJECT".
Hi JBHCSS ,
So did you create a table called Difference? This is what I did in Power Query, and the wrote the measure in Power BI. Do you know how to insert this code?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY9fC4IwFMW/yhg9jphTNB8tKAqiqAcfxIdhN432J6b1+btOUqSXnXs5v52dFQXN1KuRlNFFEIQoR/s2nXwYANfiGtCSzZgUJbfqDr0r5q7gHOUiNZ6ht9bQDfcY5wEOJ3fDXLKxY/REiDjG4bAkmVJgxvQJSKKkj7fVk2ytq+HvFcFWvP9DZmpQLSN7U+EWeWYntfZQ6kue5Qe0bDtwY5OR+JXNrcG25IpcM9Qpvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PROJECT = _t, #"BID AMOUNT" = _t, COMPANY = _t, RANK = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"PROJECT", type text}, {"BID AMOUNT", Currency.Type}, {"COMPANY", type text}, {"RANK", Int64.Type}}) in #"Changed Type"Try this, and then you can substitute your own table.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
