Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to 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!
Hi @fcanney ,
Have you found any solution?
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
96 | |
92 | |
84 | |
69 |
User | Count |
---|---|
160 | |
125 | |
116 | |
110 | |
95 |