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

## Calculate Benchmark, then identify transactions that exceed the Benchmark Value

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:

Thanks!

1 ACCEPTED SOLUTION
Frequent Visitor

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!

3 REPLIES 3
Community Support

Hi @fcanney ,

Have you found any solution?

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Frequent Visitor

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!

Frequent Visitor

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!