Stack Column Chart to count the no of projects

[h11]

Hi All,

I received an enhancement request for the solution (Solved: Re: Stacked Column Chart to show Count of Projects - Microsoft Fabric Community). Please assist me with the following:

We have two side-by-side stacked column charts; one displays all vendor sites by their total project count, while the other shows three bars representing "only vendors, vendors included, and vendors excluded."

The enhancement request is as follows:

Clicking on a specific bar (vendor site) in the left chart should display the details of that vendor site in the right chart.

For instance, clicking on the first vendor site bar, Sturgis, should display the corresponding three bars in the below requested format related to Sturgis on the right side..

Requested Format:

1st bar on right chart - Sturgis only projects

2nd bar on right chart - All vendors including sturgis projects

3rd bar on right chart - Combination of 1 and 2 i.e. All projects related to Sturgis

Note: Please utilize the below sample table provided to derive a solution.

Project Count	Vendor Site
Project A	India
Project B	India
Project C	Cootehill,Singapore,India,Arla
Project D	India
Project E	Singapore,India
Project 1	Casa Grande,Granada,Jiaxing,Singapore,India,Jelsert
Project 2	India
Project 3	India
Project 5	India
Project 6	Sturgis
Project 7	Sturgis
Project 8	AltaVista,Casa Grande,Singapore,Cootehill,Granada,Sturgis
Project 9	India, Singapore,Casa Grande
Project 10	India,Casa Grande,Granada,Jiaxing,Singapore

Thank you!

Stacked Column Chart to show Count of Projects @SamWiseOwl 

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result very clearly in a Table format.

[h11]

Hi Sam,

I tried but no luck. I used the same formula. Also applied the format, edit interaction filter on the chart and see no difference.

[SamWiseOwl]

Download this and see if there are any differences:
Vendor in list.pbix

[h11]

Thank you for sharing the PBI file. I double checked and followed the same procedure.

I'm encountering an issue where the count of projects is only displayed for a few vendors, and moreover, all three bars are showing the same count.

Clicking on each bar of the 2nd chart to view project details in the table below does not display the corresponding projects. Instead, it reverts to the original graph. To cross verify, I added a table in your power BI to see if it is working fine. Your file also didn't show the correct project details when I clicked on each bar of 2nd chart. 

[SamWiseOwl]

Hi @h11 

The structure of your file must be different to mine.

Please modify the sample file I sent you to reflect the structure of your and return it.

[SamWiseOwl]

Hi @h11 

Create a column with the 3 variants in it:

Use this as your X axis. Add a new measure =

Is Selected Vendor =

var selectedType = SELECTEDVALUE('Project Type'[Project Type])

var selectedVendor = SELECTEDVALUE('India vs Others'[Vendor Site])

RETURN

SWITCH(

    selectedType

    ,"Solo Projects"

    ,COUNTROWS(FILTER(all('India vs Others'), 'India vs Others'[Vendor Site] = selectedVendor))

    ,"Projects with others"

    ,COUNTROWS(FILTER(all('India vs Others'), CONTAINSSTRING('India vs Others'[Vendor Site],selectedVendor)))

    ,COUNTROWS(FILTER(All('India vs Others'), CONTAINSSTRING('India vs Others'[Vendor Site],selectedVendor)))

    )

Put this as your Y axis.

Make sure you turn your Left chart to interaction type FILTER:

I'm sure someone can come up with a more elegant solution!

[h11]

Hi Sam,

Thank you for this solution. I really appreciate it.

The 3rd bar in the table should not be "Without" it should be a total of 1 and 2nd bar. 

For example - Solo Projects = 5

Projects with others = 7

3rd graph should show total of 1 and 2 i.e. 5+7 = 12

Thank you!

[SamWiseOwl]

RETURN

SWITCH(

    selectedType

    ,"Solo Projects"

    ,COUNTROWS(FILTER(all('India vs Others'), 'India vs Others'[Vendor Site] = selectedVendor))

    ,"Projects with others"

    ,COUNTROWS(FILTER(all('India vs Others'),  'India vs Others'[Vendor Site] <> selectedVendor && CONTAINSSTRING('India vs Others'[Vendor Site],selectedVendor)))

    ,COUNTROWS(FILTER(All('India vs Others'), CONTAINSSTRING('India vs Others'[Vendor Site],selectedVendor)))

    )