Count of count

[Ilse_ScpDt]

Hi,

I have a table with vacanies and applicants.

Vac Appl

1     A

1     B

1     C

2     D

3     E

3     F

4     G

I counted the number of Applicants per Vacany wth following formula (calculated column):

Count=
COUNTX(
      FILTER(
             ALL(Table),
              [Vac]=EARLIER([Vac])

      ),

     [Appl]

)

This results in:

Vac Appl Count

1     A        3

1     B        3

1     C        3

2     D        1

3     E         2

3     F         2

4     G        1

Now I would like to count the count so I can make a histogram. So i would like to see how many times there is 1 Applicant, how many times there are 2 Applicants, etc. Resulting in:

Vac Appl Count Count2

1     A        3         1

1     B        3         1

1     C        3         1

2     D        1         2

3     E         2         1

3     F         2         1

4     G        1         2

Hope you can help.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating measures.

Count measure: =

VAR currentVAC =

MAX ( Data[Vac] )

VAR newtable =

FILTER ( ALL ( Data ), Data[Vac] = currentVAC )

RETURN

IF ( HASONEVALUE ( Data[Vac] ), COUNTROWS ( newtable ) )

Count2 measure: =

VAR currentCount = [Count measure:]

VAR newtable =

FILTER (

SUMMARIZE ( ALL ( data ), Data[Vac], Data[Count CC] ),

Data[Count CC] = currentCount

)

RETURN

COUNTROWS ( newtable )

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

It is for creating new columns.

Count CC =

VAR currentVAC = Data[Vac]

VAR newtable =

FILTER ( Data, Data[Vac] = currentVAC )

RETURN

COUNTROWS ( newtable )

Count2 CC =

VAR currentCount = Data[Count CC]

VAR newtable =

FILTER (

SUMMARIZE ( data, Data[Vac], Data[Count CC] ),

Data[Count CC] = currentCount

)

RETURN

COUNTROWS ( newtable )

[Ilse_ScpDt]

Hi,

Thanks for the quick reply. It is working, but is it also possible to create a measure to count the count? Because I would like to filter the result on different variables from the table, but that (ofcourse) isnt working now. 

Kind regards,

Ilse

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating measures.

Count measure: =

VAR currentVAC =

MAX ( Data[Vac] )

VAR newtable =

FILTER ( ALL ( Data ), Data[Vac] = currentVAC )

RETURN

IF ( HASONEVALUE ( Data[Vac] ), COUNTROWS ( newtable ) )

Count2 measure: =

VAR currentCount = [Count measure:]

VAR newtable =

FILTER (

SUMMARIZE ( ALL ( data ), Data[Vac], Data[Count CC] ),

Data[Count CC] = currentCount

)

RETURN

COUNTROWS ( newtable )

[Ilse_ScpDt]

uHi,

This measure is working the same as the calculated column, it doesnt react right on a filter. 

I added a column Type to illustrate:

When filtering on Type D (or B) you would like to see 1 for Count and 1 for Count2, but instead it will show 2 for Count2 (because that is the result for the total table):

How can I make this work?

[Jihwan_Kim · Accepted solution]

Hi,

If you want to add slicer, please try to use ALLSELECTED function, instead of using ALL function.

Please check the attached file.