Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
I'm totally new to Power BI and really need help with a report I'm working on. I have a dataset that contains all loans originated within a relationship (Seller Buyer), and these loans are originated in different states. My goal is to display only top 3 states for each relationship, and show the percentage of these states in the total loan count in each relationship. I was able to choose the top 3 relationships with the highest loan counts for each relationship. However, I'm having issues turning these loan counts into percentage. I tried several ways but it shows the percentage of each state in the whole dataset, not the percentage in each relationship. Is there a way for me to do so in power BI? Thanks!
Top 3 States = VAR Rankingcontext = values('Flow Database2'[State]) Return CALCULATE([Loan Count],TOPN(3, All('Flow Database2'[State]), [Loan Count]), Rankingcontext)/CALCULATE(sum('State Count'[Count]), KEEPFILTERS(values('State Count'[Seller Buyer])))
Solved! Go to Solution.
@paulvu0911 Can you please share some data and how should the expected output look like ?
Here is the simplified structured of the data. I want to see for each Seller Buyer relationship, which are the top 3 states (by loan count), and the percentage of each state in the total of loan for each relationship. So for example, for Seller Buyer 1, CA, WA, and CO should be shown as the top 3 states, with their corresponding percentage (=2/7 = 28.6%). Hope it helps!
Seller Buyer | Loan No | State |
Seller Buyer 1 | 1 | CA |
Seller Buyer 1 | 2 | CA |
Seller Buyer 1 | 3 | WA |
Seller Buyer 1 | 4 | CO |
Seller Buyer 1 | 5 | GA |
Seller Buyer 1 | 6 | CO |
Seller Buyer 1 | 7 | WA |
Seller Buyer 2 | 8 | TX |
Seller Buyer 2 | 9 | FL |
Seller Buyer 2 | 10 | TX |
Seller Buyer 2 | 11 | NC |
Thanks a million. That's exactly what I'm looking for. Just a follow up question. Is there a way to show rank the order of the table first by Seller Buyer, then by the percentage?
@ChandeepChhabra: Is there a way to embed that RankX formula in the Top 3 state measure, so that we don't need a separate column for Rank by State?
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |