Try to build a dashboard where brokerage firms can rank their sales based on either 1) total $amount of deals they bring in, or 2) # of deals they bring in.
I am building this dashboard using data from a three-level structure by joining three datasets: [Raw] join [Deal] on deal_id, and [Deal] join [Deal_Detail] on deal_id.
Because Deal to Deal_Detail is one to many, I need to use distinct_count to calculate # of deals. However, the order of built-in rank is always messed up and if I want to sort rank, the dashboard crashes every time when distinct_count is used. What could be the reason?
Hello @sunnysky230, I may need a little more information about how you are building out those joins and what exactly the error looks like that you are describing but I think I at have some ideas.
When you build out the joins in QuickSight, are you joining views/tables from your database through custom SQL or are you joining datasets directly in QuickSight without SQL? Also, what is happening in your pivot table that you are looking to apply the sort too? Pivot table visuals don’t require much complexity before issues start to occur, so I am curious how many data points you are wanting to compare.
Two things that might be worth considering. Maybe instead of altering the sort or the items displayed based on the parameter selection, you could use free form layout and add conditional rules to 2 different pivot tables. This would allow you to display one or the other already set to the required filters depending on what the user wants to view.
Also, have you tried building out a rank with the denseRank function in QuickSight and then sorting based on that field? That often tends to yield the best results when I have implemented it on my end. I hope this helps!
Hi @DylanM, we are building without custom SQL. We joined three huge tables together with various calculated fields so far. I tried the denseRank which still gives us the same issue as screenshot below. The team will try free form with conditional rules. Thanks
Hello @sunnysky230, when applying Free Form and utilizing conditional rules, were you able to achieve your expected output? Feel free to mark my above response as the solution, otherwise let me know if you continue to experience errors and what you have tried to resolve them.
Hello @sunnysky230, another thought I had on this. When you are sorting the visual, is the field that you are ranking by in the pivot table as well? Maybe you could sort descending based on that field rather than rank, allow a parameter selection for the user to determine if they will view Total $ of deal or Total count of deals, and swap between pivot table visuals depending on that selection. That may be the best route to accomplish this.
If you would like even further guidance, you can recreate this pivot table within QuickSight Arena and I can help you there. Thank you!
Thanks @DylanM , we solved it using sum(min) function. We have another sum(min) function for (total $) parallels to above (# of deal) item in a if else statement, so I’m guess we need to make them parallel to function properly. And it works! Thanks!
