PowerPivot: "COUNTIF" for same row, same table?

[DerekK]

Good day,

I thought this would be easy but I'm having a devil of a time, I hope someone can please help!

I've tried writing this in a table; within PowerPivot; and as a measure but nothing seems to elicit what I'm after. (I'm new to PowerPivot, so I may be overlooking something obvious.) I'm working with Excel 2016, if that helps.

So, I have a column that lists "Region" (text) and I need to have a column that counts the number of times each region appears in the entire column. In a normal Excel spreadsheet, I might enter a formula like the following and copy it down:

=COUNTIF($C$1:$C$100,C1)

I've tried CALCULATE and COUNTROWS with a filter but it doesn't look quite right because it comes out with something like "[Region]=[Region]" and it elicits a figure which would be COUNTROWS without any filters.

(I'm actually looking for something to put into a Pivot Table's "Rows" section, so the final formula would be something like):

=C1 & " (" & COUNTIF($C$1:$C$100,C1) & " users)"

Hope you can help!

Sincerely,



Derek

 

[VBA Geek]

based on what you are describing you just need to add a measure: = COUNTROWS(YourTableName)

then drop the Region in the rows of the pivot table and this measure as values

 

[DerekK]

Thank you for your reply, VBA Geek.

That would, indeed, give me the figures I need but, ultimately, I want to have a chart whose axis label would be something like this:

=C1 & " (" & COUNTIF($C$1:$C$100,C1) & " users)"

 

[VBA Geek]

Do you mean you want a pivot table like this?

 

[DerekK]

Something more like this (I managed to get this one through a more difficult, roundabout way but I literally have dozens of these to do, so a simpler way would be very much appreciated):

THANK YOU FOR YOUR CONTINUED PATIENCE!

 

[VBA Geek]

Then you need to add a calculated column with this DAX


=<br>Regions[Region] & <span class="StringLiteral" style="color:#D93124">" "</span><br>    & <span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">COUNTROWS</span><span class="Parenthesis" style="color:#969696"> (</span> Regions <span class="Parenthesis" style="color:#969696">)</span>,<br>        Regions[Region] = <span class="Keyword" style="color:#0070FF">EARLIER</span><span class="Parenthesis" style="color:#969696"> (</span> Regions[Region] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">ALL</span><span class="Parenthesis" style="color:#969696"> (</span> Regions <span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br>    & <span class="StringLiteral" style="color:#D93124">" users"</span><br>

 

[DerekK]

Yep! Works like a charm!

Thank you most kindly, VBA Geek!