Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

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

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!

## Helpful resources

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors