How to count the occurrence of values in a column and display total correctly in same table

[Anonymous]

Hi all, 

I've got stuck trying to figure this out and was wandering if anyone could help guide me in the right direction.

I've got some project data like below. Each project has a corresponding point value which denotes how critical that project is. In the database I have hundreds of projects so I have slicers on the report that allows me to zoom in to certain types of project on my will.

Raw Data

The end result I would like to have is as below. In case I choose to look at just the first 4 projects, the count should be able to change dynamically as well.

CountsFirst 4 projects with counts

Any suggestions on how to tackle this will be deeply appreciated.

Cheers,

[OwenAuger · Accepted solution]

Hi @Anonymous

It looks like you want a measure that returns the number of projects with the same Points as the currently filtered Project, within the set of projects selected in the visual.

Assuming your table is called Projects and it contains one row per Project, this measure should do that:

Count =
CALCULATE (
    COUNTROWS ( Projects ),
    ALLSELECTED ( Projects ),
    VALUES ( Projects[Points] )
)

This measure sets the filter context on the Projects table to "all selected Projects", and adds a filter on the Points column corresponding to currently filtered Projects, then counts the Projects in that context.

Cheers,

Owen 🙂

[ptapurva]

Hello Owen,

What if there are multiple rows for each project but I want the count to repeat in all the rows for that project?

Thanks!

[ptapurva]

Hello Owen,

What if there are multiple rows for each project but I want the count to repeat in all the rows for that project?

Thanks!

[inkyphysician]

Just what I needed today too.  I knew it would be a simple looking formula.

[sf17]

When I perform this, all values end up counting the full set of rows (9). It's not filtered for only the values that match the current row; would expect values of 3.

=CALCULATE(COUNTROWS(Query1),ALLSELECTED(Query1),VALUES(Query1[Alias]))

DATA:

AssignmentID	Alias	Title	AccountID
953202	abcde	Account Manager	8825321
955780	bcdef	Account Manager	5305253
956871	cdefg	Account Manager	8688920
958597	abcde	Account Manager	1566066
961119	bcdef	Account Manager	9647982
962453	cdefg	Account Manager	2401429
967272	abcde	Account Manager	1511273
971636	bcdef	Account Manager	4349538
972643	cdefg	Account Manager	1426770

[Anonymous]

Hi @OwenAuger,

As I am developing my reports new problems come up and I wonder if you could shed some light again. Even if you could point me a direction that will be much appreciated.

Thanks for your porvided DAX I now have the correct count however I need to be able to differentiate those with the same count. Something like below. I would also need to be able to dynamically filter the column through slicers.

RankRank with filter

Thanks,

[Anonymous]

Hi @OwenAuger,

Thanks for your reply. It works for me!!! 😄

Cheers,

[Anonymous]

Hi,
I think you could create a Dynamic table.

Step 1: create a new coloumm "count_temp" =1
Step 2 with SUMMARIZE function starting from your row data table crete the count value: CountTable =summarize(ProjectTable, ProjectTable [Points], "Count",SUM(ProjectTable [count_temp]))
Step 3: create a relationships betweem two tableau (double side) - Points to Points
Step 4: add you coloumn to your table

Hope It Will be useful