Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Count Values In Each Row (4 options) need it for stack bar chart

Hello,

 

I have a ticketing system I made with four options currently. ABF, GPON, Light Green and Not available on each address that is submitted for review.

My data is coming from a sharepoint list into power bi. 

 

I need a way to count the number of times each word is in here per "Ticket Status". (Across if that makes sense) 

I will attatch a screenshot. I am trying to create a bar chart that will tell me how many were ABF, GPON, Light Green and Not available per ticket status. I will attatch photos to help with this. Basically I need to count the values for Completed. 

 

Things to note, 

 

I have 40 "Potential Address Results" columns #1-#40

and I have a TicketStatus Column with options unassinged, assigned, working, and completed. 

 

Completed is the only important one here 

 

I cannot unpivot the data 

 

I need it to look this like... please help! 

 

Thank you 

9 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 

    best option is to unpivot the 40 columns using power query. Once done the rest is simple. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      tamerj1 

      Stupid question.. how do I do that? 

       

      What I was trying to do now was take each column something like 

      Calculate(Countrows( Table name ( column ) = "ABF") 

       

      But if this is easier I will totally do it! You have no idea how much you are helping me. 

       

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous 

        Open the query editor.

        Select the 40 columns > right click > select unpivot

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    Could you share relevant screenshots to further clarify your question? Please save the screenshot locally before uploading,

    Best Regards,
    Community Support Team _ Eason