Count the number of value in one column based on another

[ThomasDaSilva]

Hi everyone,

I'm trying to create a formula but i need some help.

I have a table with a list of client number and paiement code.

Each client can have different paiement code:

Code client      paiement Code

10250                    Z1

10250                   Z2

10251                   Z3

10252                   Z4

10252                  Z1

My goal is to count the number of different paiement code for each client:

10250             2

10251            1

10252            2

I've tried measures but without success.

Thank you all 

[Anonymous]

i have a table like this

ID

	Surname	First Name
1234567	abc	def
1234567	abc	def
1234567	abc	def
1234567	abc	def
890123	ghk	lmk
890123	ghk	lmk
890123	ghk	lmk
1111111	ggg	hhh
1111111	ggg	hhh

Need a table like this (add a column and count based on column ID

ID	Surname	First Name	Count
1234567	abc	def	4
1234567	abc	def	4
1234567	abc	def	4
1234567	abc	def	4
890123	ghk	lmk	3
890123	ghk	lmk	3
890123	ghk	lmk	3
1111111	ggg	hhh	3
1111111	ggg	hhh	2

[Ashish_Mathur]

Hi,

Write this calculated column formula

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])))

Hope this helps.

[Anonymous]

Smart solution, I tried in one of my works. It is awsome 😊

[Ashish_Mathur]

Thank you.

[Anonymous]

Thanks Ashish ... what is Data refering to ? remember i need to create an additional column aswell.

[Ashish_Mathur]

You are welcome.  That is the name of the Table.  Change it to whatever your Table name is.

[Anonymous]

Thank you

[Ashish_Mathur]

Hi,

If my reply helped, please mark it as Answer.

[Ashish_Mathur · Accepted solution]

Hi,

Drag Code Client to the Row labels and use this measure

=DISTINCTCOUNT(Data[paiement code])

[newpowerbiuser8]

Hello! 

I want to calculate distinct values of column A. Which i did by using the distinct function in a measure I created. 

Now I want to calculate the total number of "No" for each unique value only. Can anyone please help? @Ashish_Mathur @v-danhe-msft 

For example the total should be 3 for "No" as I want to count for Saturday as only 1 not 2 times. 

[Ashish_Mathur]

Hi,

Drag the Not applicable column to the table visual and write this measure

Measure = distinctcount(Data[Day])

Hope this helps.

[newpowerbiuser8]

Thanks Ashish, my distinct count is working now but for some reason in both columns it is giving me 1 more value. For example, I checked in excel, where i deleted the duplicate values, the answer excel is giving is 10 hoever my distinct count is giving 11

[Ashish_Mathur]

Hi,

That should not be happening.  Share the download link of the PBI file.

[v-danhe-msft]

Hi @ThomasDaSilva,

Based on my test, you can refer to below steps:

1.I have entered some sample data like the below picture:

2.

2.Create a measure.

Measure = CALCULATE(COUNT(Table1[Code client]),FILTER('Table1','Table1'[Code client]<=MAX('Table1'[Code client])))

3.Create a Table visual and add the related field, now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ttunta8z8slai36/Count%20the%20number%20of%20value%20in%20one%20column%20ba...

Regards,

Daniel He

[ThomasDaSilva]

Hi

Thank you for your answer.

Your measure work but i forgot to mention that i want to be able to create a slicer with the number of paiement code different to see which client have for example four paiement code. I think the best is a calculated column instead of a measure.

[v-danhe-msft]

Hi @ThomasDaSilva,

could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft]

Hi @ThomasDaSilva,

Based on my test, you can refer to below steps:

1.You could create a calculated column.

Count = CALCULATE(COUNT(Table1[Paiement Code]),FILTER('Table1','Table1'[Code client]=EARLIER(Table1[Code client])))

2.Create a Table visual and add the related fields, now you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/8zx0ingb8m6x51b/Count%20the%20number%20of%20value%20in%20one%20column%20ba...

Regards,

Daniel He