Forum Discussion
Cross Filtering with Pie Chart containing Total Values
I am preparing a dashboard of investment returns and need help with establishing a connection for cross-filtering.
I have a dataset with all the various banks/portfolios with total values along with a breakup of the totals based on the asset class.
2 separate charts are required:
1. A pie chart with total values by the bank.
2. A bar chart with values by asset class.
When I click on any bank in the pie chart, it successfully filters the bar chart to show the breakup of values of that bank only in the bar chart. The issue is when I click on a particular asset class in the bar chart, it does not filter to show the breakup of that asset class only by the different banks in the pie chart. Below is a sample data set. Please help - how do I establish a connection here so that cross filter works both ways.
For reference - the pie chart would have only total values (total of all asset classes) by the bank, whereas the stacked bar chart would have values by asset class by financial year.
| Bank / Portfolio | FY | Cash | Fixed Deposits | Others | Structured Notes | Fixed Income | Alt/Hedge Funds | Public Equity | Private Equity | TOTAL |
| HSBC Spore | 2021 | 4,037,275 | 4,401,288 | 3,559,475 | 483,031 | 365,791 | 1,593,930 | 2,143,352 | 16,584,142 | |
| HSBC Spore HYB | 2021 | 3,758,628 | 4,215,307 | 1,588,884 | 645,470 | 2,292,210 | 3,370,844 | 1,925,010 | 17,796,353 | |
| UBP | 2021 | 1,334,995 | 3,661,374 | 1,379,385 | 440,786 | 3,337,925 | 3,964,971 | 2,849,444 | 16,968,880 | |
| BOS | 2021 | 1,055,665 | 2,754,764 | 2,745,416 | 793,893 | 7,349,738 | ||||
| ASK | 2021 | 60,879 | 2,587,276 | 374,207 | 3,477,279 | 6,499,641 | ||||
| UBS | 2021 | 2,420,478 | 533,097 | 3,159,985 | 6,113,560 | |||||
| UBS 02 USD | 2021 | 4,804,157 | 4,411,126 | 4,396,893 | 13,612,176 | |||||
| UBS 04 AUD | 2021 | 790,635 | 3,935,849 | 1,256,878 | 5,983,362 | |||||
| Julius Baer | 2021 | 4,269,837 | 767,986 | 2,110,941 | 7,148,764 | |||||
| Zurich | 2021 | 2,944,542 | 4,092,349 | 1,897,893 | 8,934,784 | |||||
| Pabrai | 2021 | 163,820 | 4,699,877 | 3,065,621 | 7,929,318 | |||||
| DBS | 2021 | 4,153,268 | 2,613,820 | 6,767,088 |
Is this what you are after?
If so, you need to unpivot all the columns in Power Query except for Bank/portfolio and FY. (and filter out the TOTAL column)
Create the measure:
Sum Values = SUM(SampleTable[Value])Then use the Attribute field as the axis or legend in the clustered column chart.
3 Replies
Hi, jeromesavio3
After my test, I think it is not a cross filter problem, because when you click on one asset class, originally each bank will respond to, so there is no problem with the display, I don’t know what result you want to get. If you can show it?
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,Community Support Team _ Janey
Hi - so what I was looking for was the ability to cross filter in both directions:
1. So when you click on a bank in the pie chart - it filters to show the various asset classes in the bar chart under that bank - this is not an issue and I was able to do this -
2. When you click on an asset class in the bar chart - I would like it to filter to only show the banks which have this asset class along with the relative proportions - this is what I was unable to do:
See below screenshot where Private equity is selected but no change/filter applied to the pie chart:
The issue is that the pie chart only has total values by a bank, whereas the bar chart has individual values that make up that total but there is no connection established that the total values in the pie chart are comprised of the amounts under the various asset classes.
Is this what you are after?
If so, you need to unpivot all the columns in Power Query except for Bank/portfolio and FY. (and filter out the TOTAL column)
Create the measure:
Sum Values = SUM(SampleTable[Value])Then use the Attribute field as the axis or legend in the clustered column chart.
