cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

4 REPLIES 4
Super User

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

Proud to be a Super User!

Super User

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

Proud to be a Super User!

Frequent Visitor

I'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".

Super User

Your 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.
Nathaniel

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.