Find the difference between values in a group

[JBHCSS]

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.

[Nathaniel_C]

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

[Nathaniel_C · Accepted solution]

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

[JBHCSS]

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

[Nathaniel_C]

@JBHCSS,

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