Forum Discussion

Tentoria's avatar
Tentoria
Frequent Visitor
2 years ago
Solved

Power BI count unique values in a column from SharePoint list

Hi everyone,   I have a SharePoint list with multiple (choice) columns, each column has different values (C/CWF/DEV/NA). I want to divide the columns into 4 different bar charts where every chart d...
Tentoria's avatar
Tentoria
Frequent Visitor
2 years ago
FTT NAMEPTT NAMEPTT GRADEDATEProactively supports co-trainer(s)Follows lead trainer guidance /decisionsCommunicates with lead trainer in a timely manner
User 1PTT1PUR45267CCC
User 1PTT2GR145266.3333CWFCWFCWF
User 1PTT3CSV45268CCC
User 2PTT4PUR45268CCC
User 3PTT5PUR45268CCCWF
User 4PTT6CSV45268CCC
User 5PTT7PUR45261CCC
User 6PTT8GR145267.3333DEVCC
User 7PTT9PUR45268CCC
User 1PTT10PUR45268CCC
User 1PTT11CSV45268CCC
User 3PTT2PUR45268CCC
User 2PTT12PUR45268CCC
User 8PTT13GR145268CCC
User 9PTT1PUR45287CCC
User 4PTT14CSV45287CCC
User 5PTT15FG145294CCC
User 2PTT16FG145301CCC

 

Thank you for your reply lbendlin .

Above is a sample data of a bigger table that I have on the SharePoint list. Out of this data I am generating a Power BI dashboard like the below:

 

Everything is ok on the dashboard except for the 4 visuals (Collaboration, Knowledge, Delivery and Drive). Based on the sample table provided, the last 3 columns are under the collaboration visual which I want it to display like the visual below:

 

Help me with that and I will follow the same for the other 3 visuals.

 

Let me know if you need any more information.

 

Thank you 🙂

lbendlin's avatar
lbendlin
Super User
2 years ago

Unpivot your data to bring it into usable format.

 

 

see attached

  • Tentoria's avatar
    Tentoria
    Frequent Visitor
    2 years ago

    Hi lbendlin ,

     

    Thank you for your reply.

     

    I did that and it worked fine for the 1st visual "Collaboration". A soon I did the same for the other 3 visuals (Knowledge, Delivery and Drive) it duplicated the rows resulting to innacurate values in the visuals. So instead of having a total of 18 for each column, it gave 2394 and other higher values. If I click on the row and remove duplicates it removes necessary rows which again results in inaccurate values.

     

    Is there a way to share the file with you so you can have a better understanding and maybe fix it?

     

    Thank you.

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      Please provide sample data that fully covers your issue.
      Please show the expected outcome based on the sample data you provided.

      • Tentoria's avatar
        Tentoria
        Frequent Visitor
        2 years ago

        Hi lbendlin ,

         

        FTT NAMEPTT NAMEPTT GRADEDATECOLLABORATIONProactively supports co-trainer(s)Follows lead trainer guidance /decisionsCommunicates with lead trainer in a timely mannerKNOWLEDGEDelivers correct SEP knowledgeAble to provide additional knowledge (apart from the OME)Answers questions confidently and knowledgeably DELIVERYAble to objectively evaluate leaner(s) performanceAble to respectfully deliver objective /constructive feedback to learners during the practical sessionsRespectfully deliver theory sessionsCommunicates with learners appropriatelyAdheres to approved timetable /training material(s)Manages all training resources efficiently
        HusseinAhmed PUR07/12/2023 00:00:00 CCC CCC CCCCCC
        HusseinEl IasGR106/12/2023 00:00:00 CWFCWFCWF CCC CCCCCC
        HusseinWissam CSV08/12/2023 00:00:00 CCC CCC CCCCCC
        Tilina Yvonne PUR08/12/2023 00:00:00 CCC CCC CCCCCC
        Tufan Roxanne PUR08/12/2023 00:00:00 CCCWF CCC CWFCCCCC
        Trevor Saba CSV08/12/2023 00:00:00 CCC CWFCWFCWF CCCCCC
        Xiyao Sean PUR01/12/2023 00:00:00 CCC CCC CCCCCC
        Wael Francesca  GR107/12/2023 00:00:00 DEVCC CCC CCCCCC
        Mary Mijat PUR08/12/2023 00:00:00 CCC CCC CCCCCC
        HusseinMichael PUR08/12/2023 00:00:00 CCC CCC CCCCCC
        HusseinJanka CSV08/12/2023 00:00:00 CCC CCC CCCCCC
        HusseinDeon PUR08/12/2023 00:00:00 CCC CCC CCCCCC
        HusseinManpreet PUR08/12/2023 00:00:00 CCC CCC CCCCCC
        Orsolya Maleena GR108/12/2023 00:00:00 CCC CCWFC CCCCCC
        Christian AhmedPUR27/12/2023 00:00:00 CCC DEVCWFC CCCCCC
        HusseinAlexandra CSV27/12/2023 00:00:00 CCC CCC CCWFCCCWFC
        HusseinShauna  FG103/01/2024 00:00:00 CCC CCWFDEV CCCCCCWF
        HusseinLejla FG110/01/2024 00:00:00 CCC CWFCWFC DEVCCCCC
        HusseinAnnaGR111/01/2024 00:00:00 CCC CCC CCCCCWFC

         

        This is the table before unpivoting as I couldn't upload it due to the duplicates. Please unpivot the columns and see the duplicates.

         

        Desired outcome: