Power BI count unique values in a column from SharePoint list

[Tentoria]

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 displays on the X axis the names of the columns and the Y axis displays the bars where each bar shows the total number of different values in that specific column so I guess the bar would have 4 different colors. 

Below is the table (it has more columns):

I want something similar to the below, basically I will need to create 4 of the below.

It is important that the charts update whnever a new entry is added to the list.

Thank you and I loof forward to your support.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Tentoria]

FTT NAME	PTT NAME	PTT GRADE	DATE	Proactively supports co-trainer(s)	Follows lead trainer guidance /decisions	Communicates with lead trainer in a timely manner
User 1	PTT1	PUR	45267	C	C	C
User 1	PTT2	GR1	45266.3333	CWF	CWF	CWF
User 1	PTT3	CSV	45268	C	C	C
User 2	PTT4	PUR	45268	C	C	C
User 3	PTT5	PUR	45268	C	C	CWF
User 4	PTT6	CSV	45268	C	C	C
User 5	PTT7	PUR	45261	C	C	C
User 6	PTT8	GR1	45267.3333	DEV	C	C
User 7	PTT9	PUR	45268	C	C	C
User 1	PTT10	PUR	45268	C	C	C
User 1	PTT11	CSV	45268	C	C	C
User 3	PTT2	PUR	45268	C	C	C
User 2	PTT12	PUR	45268	C	C	C
User 8	PTT13	GR1	45268	C	C	C
User 9	PTT1	PUR	45287	C	C	C
User 4	PTT14	CSV	45287	C	C	C
User 5	PTT15	FG1	45294	C	C	C
User 2	PTT16	FG1	45301	C	C	C

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]

Unpivot your data to bring it into usable format.

see attached

[Tentoria]

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]

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

[Tentoria]

Hi @lbendlin ,

FTT NAME

PTT NAME

PTT GRADE

DATE

COLLABORATION

Proactively supports co-trainer(s)

Follows lead trainer guidance /decisions

Communicates with lead trainer in a timely manner

KNOWLEDGE

Delivers correct SEP knowledge

Able to provide additional knowledge (apart from the OME)

Answers questions confidently and knowledgeably

DELIVERY

Able to objectively evaluate leaner(s) performance

Able to respectfully deliver objective /constructive feedback to learners during the practical sessions

Respectfully deliver theory sessions

Communicates with learners appropriately

Adheres to approved timetable /training material(s)

Manages all training resources efficiently

Hussein

Ahmed

PUR

07/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Hussein

El Ias

GR1

06/12/2023 00:00:00

CWF

CWF

CWF

C

C

C

C

C

C

C

C

C

Hussein

Wissam

CSV

08/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Tilina

Yvonne

PUR

08/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Tufan

Roxanne

PUR

08/12/2023 00:00:00

C

C

CWF

C

C

C

CWF

C

C

C

C

C

Trevor

Saba

CSV

08/12/2023 00:00:00

C

C

C

CWF

CWF

CWF

C

C

C

C

C

C

Xiyao

Sean

PUR

01/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Wael

Francesca

GR1

07/12/2023 00:00:00

DEV

C

C

C

C

C

C

C

C

C

C

C

Mary

Mijat

PUR

08/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Hussein

Michael

PUR

08/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Hussein

Janka

CSV

08/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Hussein

Deon

PUR

08/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Hussein

Manpreet

PUR

08/12/2023 00:00:00

C

C

C

C

C

C

C

C

C

C

C

C

Orsolya

Maleena

GR1

08/12/2023 00:00:00

C

C

C

C

CWF

C

C

C

C

C

C

C

Christian

Ahmed

PUR

27/12/2023 00:00:00

C

C

C

DEV

CWF

C

C

C

C

C

C

C

Hussein

Alexandra

CSV

27/12/2023 00:00:00

C

C

C

C

C

C

C

CWF

C

C

CWF

C

Hussein

Shauna

FG1

03/01/2024 00:00:00

C

C

C

C

CWF

DEV

C

C

C

C

C

CWF

Hussein

Lejla

FG1

10/01/2024 00:00:00

C

C

C

CWF

CWF

C

DEV

C

C

C

C

C

Hussein

Anna

GR1

11/01/2024 00:00:00

C

C

C

C

C

C

C

C

C

C

CWF

C

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:

[lbendlin · Accepted solution]

something like this?

[Tentoria]

Yes!!

So how can I do it on my dashboard? as the table I have has more columns.

How can I share with you my pbix file?

[lbendlin]

The process is the same, regardless of how many columns you have. 

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Tentoria]

Here is the link to the file.Thank you and Happy new year 🙂

https://we.tl/t-MBT7wSj7DH 

[lbendlin]

Instead of unpivoting the individual sections separately you need to unpivot all at once.

Can you provide a sanitized extract of your SharePoint list?

[Tentoria]

Hi @lbendlin ,

I have provided the SharePoint list as requested. Did you manage to open it?

[lbendlin]

No, I have to check how to hande list templates, a first for me.

[Tentoria]

Appreciate your help 🙂

[Tentoria]

Hi @lbendlin ,

I tried unpivoting all at once but how do I select which ones go in each visual?

Here is the link to sharepoint list : https://we.tl/t-I097XfqURB