Stacked Column Chart to show Count of Projects

[h11]

Hi Team,

I have more than 100+ in process projects and I want to show a stack column chart of Project count per vendor site. This is just a sample of 14 projects. I need to show the count of Indian projects vs all other projects in the same stacked column chart.

Project Title	Vendor Site
Project A	India
Project B	USA
Project C	Japan
Project D	Singapore
Project E	Canada
Project 1	Canada
Project 2	India
Project 3	Canada
Project 5	USA
Project 6	Japan
Project 7	Canada
Project 8	India
Project 9	Japan
Project 10	India

From the above table it is clear that india has 4 projects. So I want a formula to show a column chart where I can show 4 vs 10 (i.e. India vs non Indian Projects count)

Thanks in advance

[h11]

Hi @SamWiseOwl 

I received an enhancement request for the current solution. 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 related to Sturgis on the right side..

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

1st bar on 2nd chart - Sturgis only projects

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

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

[h11]

Hi Sam,

Thank you for your response. I apologise if I haven't conveyed my question right. All I want to show is India vs non indian projects on the same stacked column chart i.e ( India projects count vs Canada+US+Japan+Singapore projects counts). So, I should be having only two Columns ( One showing only India count and the other showing all non indian vendors together in the same chart) not indiviual columns for all 5 vendors seperately. 

Thank you.

[SamWiseOwl]

HI @h11 

Do the technique shown in the Pie chart but do it in a column chart:

Or do it directly in the data, right click the Vendor Site column choose New group:

click on India and choose Group.

Use the newly created column in your visual.

Close it down and you will have a new column in your data:

Use this column in your column chart 🙂

[h11]

Thank you Sam, I'm almost there!!

Now, Let me get into a bit more detail about my requirements. As I mentioned in my earlier post, I have more than 100+ projects. I need to show the column chart by India only projects vs Other vendors including india projects vs only non indian vendor projects.  From the below case we should have the columns as ( 6 vs 5 vs 3)

Thank you once again for helping me 🙂

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	Zwolle
Project 7	Zwolle
Project 8	AltaVista,Casa Grande,Singapore,Cootehill,Granada,Sturgis
Project 9	India, Singapore,Casa Grande
Project 10	India,Casa Grande,Granada,Jiaxing,Singapore

[SamWiseOwl]

Hi @h11 
Create a new calculated column like this:

Vendor type =

SWITCH(

    TRUE()

    ,[Vendor Site] = "India", "India only projects "

    ,Not(CONTAINS('India vs Others',[Vendor Site],"India")) , "Only non indian vendor projects"

    ,"Other vendors including india projects"

)

[h11]

Sam,

I applied the same formula in my Power BI, but I'm puzzled by the 'India vs Other' parameter within it. I'm getting an error if I use it. I'm unsure why it was included or what should be substituted for it. Could you clarify its purpose so I can adapt the formula to my own terminology?

SWITCH(

    TRUE()

    ,[Vendor Site] = "India", "India only projects "

    ,Not(CONTAINS('India vs Others',[Vendor Site],"India")) , "Only non indian vendor projects"

    ,"Other vendors including india projects"

)

Thank you!

[SamWiseOwl]

Ah apologises that is the name of your table 🙂 change it to what ever is correct.

[h11]

Thank you. I used the same exact formula with my table and column names but there is a problem, I'm not getting a seperate value for "Other plants including india projects" and 

"Only non indian vendor projects". I'm getting a combined value of 302 projects with the name "Other plants including india projects" and when I checked the table I see that for all non indian vendor projects without india, got the same name "Other plants including india projects" in the calculated column I added . Please help.

[SamWiseOwl · Accepted solution]

SWITCH(

    TRUE()

    ,[Vendor Site] = "India", "India only projects "

    ,Not(CONTAINSString(Table[Vendor Site],"India")) , "Only non indian vendor projects"

    ,"Other vendors including india projects"

)

Try this instead 😁

[h11]

Perfect! You are awesome! I got it! Thanks a ton!

[SamWiseOwl]

Hi @h11 

The easiest thing to do is right click on India within the chart and choose Group data:

It will look like thiS:

 Alternatively you could have a measure that works out how many are NOT the current Vendor and use it in the same column chart:

Non Project = CALCULATE(Count('Project Table'[Project Title]), 'Project Table'[Vendor Site] <> SELECTEDVALUE('Project Table'[Vendor Site]))