Calculate Benchmark, then identify transactions that exceed the Benchmark Value

[fcanney]

I think I'm close to the answer, but can't quite figure this out.  Would appreciate any help!

I have a table of transactions with multiple buyers and multiple sellers (sample set below).

Transaction	$ value	Buyer1	Buyer2	Seller1	Seller2
1	10	A		B
2	100	B	C	A
3	20	A		B
4	30	B		C	D
5	150	C	A	B
6	250	A		D
7	125	D		A	B
8	35	D		A
9	55	B	C	A
10	70	A		D	B

Using this link: https://www.sqlbi.com/articles/creating-a-slicer-that-filters-multiple-columns-in-power-bi/, I successfully created a single buyer and seller slicer to filter the data set.

Now I'm trying to calculate the median sale value for each seller across all of their transactions, and highlight if a given purchase made by that seller exceeds that median sale value for that party.  

	Median Sale Value
A	77.5
B	70
C	30
D	70

Given the median sale value for person A is $77.5 (median of transactions #2, 7, 8, 9).  Using person A, the new metric or column would highlight that transactions 1, 3 and 10 are below the median for A, and transaction 5 and 6 exceed the median.  Final result would be something like this:

						Buy exceeds Median Sale?
Transaction	$ value	Buyer1	Buyer2	Seller1	Seller2	Buyer1	Buyer2	Either
1	10	A		B		0	null	0
2	100	B	C	A		1	1	1
3	20	A		B		0	null	0
4	30	B		C	D	0	null	0
5	150	C	A	B		1	1	1
6	250	A		D		1	null	1
7	125	D		A	B	1	null	1
8	35	D		A		0	null	0
9	55	B	C	A		0	1	1
10	70	A		D	B	0	null	0

Sample data file and simple PowerBI here:

https://drive.google.com/drive/folders/1r6Z9BXp17s4v2CbA_LM2rCypOCeVQAZw?usp=sharing

Thanks!

[v-jayw-msft]

Hi @fcanney ,

Have you found any solution?

[fcanney]

Hi @v-jayw-msft - Unfortunately, no solution yet. 

Breaking this into multiple steps, I think if we can create a table with each person as a distinct row:

AllBuyersSellers = DISTINCT ( UNION ( SELECTCOLUMNS ( Transactions, "AllBuyerSeller" = Transactions[Buyer1]) , SELECTCOLUMNS ( Transactions, "AllBuyerSeller" = Transactions[Buyer2]), SELECTCOLUMNS ( Transactions, "AllBuyerSeller" = Transactions[Seller1]), SELECTCOLUMNS ( Transactions, "AllBuyerSeller" = Transactions[Seller2] ) ) )

But then the question is how to calculate a median sale value for each row of the new table?  This is where I'm stuck

Once I have a median sale value for each row of the table, then I can create a new set of columns in the original Transactions table using Buyer1 Median Price = LOOKUPVALUE ( AllBuyersSellers[MedianSale], AllBuyersSellers[AllBuyerSeler],Transactions[Buyer1]].  Repeat this for each buyer column, then do a custom formula to identify if any of the purchase values exceed the median for any of the buyers in a given transaction. 

So the real issue is how to calculate a median using the buyer name as the filter, when that buyer name can show up in multiple columns in the source table.

Thanks for any help you can provide!

[fcanney · Accepted solution]

I managed to figure this one out - no need to post a solution.

I solved the issue by adding a new calculated column to my all_sellers table that calculates the average sale price of all transactions for each distinct seller.  Then I used a lookupvalue to pull that average by seller into the transaction table, looking up each buyername in the all_seller table and pulling the average of sale values (2 new calculated columns - one for each buyer).  I then created another calculated column that compares the average sale values against the buying activity to highlight purchases that exceed the average. 

Seems to be working, but is a bit cumbersome - I'm sure there is a better way.  But at least it works.

Thanks!