Forum Discussion

h11's avatar
h11
Icon for Helper III rankHelper III
2 years ago

Stack Column Chart to count the no of projects

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 CountVendor Site
Project AIndia
Project BIndia
Project CCootehill,Singapore,India,Arla
Project DIndia
Project ESingapore,India
Project 1Casa Grande,Granada,Jiaxing,Singapore,India,Jelsert
Project 2India
Project 3India
Project 5India
Project 6Sturgis
Project 7Sturgis
Project 8AltaVista,Casa Grande,Singapore,Cootehill,Granada,Sturgis
Project 9India, Singapore,Casa Grande
Project 10India,Casa Grande,Granada,Jiaxing,Singapore

 

Thank you!

 

Stacked Column Chart to show Count of Projects SamWiseOwl 

Need Help

8 Replies

  • h11's avatar
    h11
    Icon for Helper III rankHelper III
    2 years ago

    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.

     

     

      • h11's avatar
        h11
        Icon for Helper III rankHelper III
        2 years ago

        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's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      h11
      Icon for Helper III rankHelper III
      2 years ago

      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's avatar
        SamWiseOwl
        Icon for Super User rankSuper User
        2 years ago

        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)))

            )

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

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