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.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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