Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jeromesavio3
New Member

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 / PortfolioFY Cash  Fixed Deposits  Others  Structured Notes  Fixed Income  Alt/Hedge Funds  Public Equity  Private Equity  TOTAL 
HSBC Spore20214,037,275 4,401,2883,559,475483,031365,7911,593,9302,143,35216,584,142
HSBC Spore HYB20213,758,628 4,215,3071,588,884645,4702,292,2103,370,8441,925,01017,796,353
UBP20211,334,995 3,661,3741,379,385440,7863,337,9253,964,9712,849,44416,968,880
BOS2021   1,055,6652,754,7642,745,416793,893 7,349,738
ASK2021   60,8792,587,276374,2073,477,279 6,499,641
UBS2021   2,420,478533,097 3,159,985 6,113,560
UBS 02 USD2021   4,804,1574,411,126 4,396,893 13,612,176
UBS 04 AUD2021   790,6353,935,849 1,256,878 5,983,362
Julius Baer2021   4,269,837767,986 2,110,941 7,148,764
Zurich 2021   2,944,5424,092,349 1,897,893 8,934,784
Pabrai2021   163,8204,699,877 3,065,621 7,929,318
DBS2021   4,153,2682,613,820   6,767,088

 

1 ACCEPTED SOLUTION

Is this what you are after?

Crossfilter.gif

If so, you need to unpivot all the columns in Power Query except for Bank/portfolio and FY. (and filter out the TOTAL column)
PQ.JPG

Create the measure:

Sum Values = 
SUM(SampleTable[Value])

Then use the Attribute field as the axis or legend in the clustered column chart. 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

3 REPLIES 3
v-janeyg-msft
Community Support
Community Support

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?

vjaneygmsft_0-1631756461855.png

vjaneygmsft_1-1631757299248.png


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 -

jeromesavio3_0-1632038820336.png

 

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:

jeromesavio3_1-1632038967086.png

 

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?

Crossfilter.gif

If so, you need to unpivot all the columns in Power Query except for Bank/portfolio and FY. (and filter out the TOTAL column)
PQ.JPG

Create the measure:

Sum Values = 
SUM(SampleTable[Value])

Then use the Attribute field as the axis or legend in the clustered column chart. 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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