Skip to main content
cancel
Showing results for 
Search instead 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

Reply
fcanney
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$ valueBuyer1Buyer2Seller1Seller2
110A B 
2100BCA 
320A B 
430B CD
5150CAB 
6250A D 
7125D AB
835D A 
955BCA 
1070A DB

 

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
A77.5
B70
C30
D70

 

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$ valueBuyer1Buyer2Seller1Seller2Buyer1Buyer2Either
110A B 0null0
2100BCA 111
320A B 0null0
430B CD0null0
5150CAB 111
6250A D 1null1
7125D AB1null1
835D A 0null0
955BCA 011
1070A DB0null0

 

Sample data file and simple PowerBI here:

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

 

Thanks!

1 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!

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
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.

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!

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors