Count distinct rows for sub total

agasi

New Member
Joined
Nov 22, 2016
Messages
48
I want to count all rows. Distintcount works but when it comes down to sub total, if there is no value, it does not include the particual row.

Let's say: Project A for c person has not got a value. So when I use distintcount (level), it won't add C in Project A/Count in green. I need the High total for Project A/count in green should be 3 not 2. Regardless of the values, it just needs to count the total rows per level. Has anyone got an idea?

levelStaffProject A /valueProject B /valueProject A /countProject B/count
higha24511
b102511
c101
High Total128023

<tbody>
</tbody>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
In Powerpivot you can write a measure for your counts and sums.
Your data needs to be cleaned up for the Powerpivot model.
Using PowerQuery, the null values have been replaced by '0' and the level value 'high' has been copied down the column.


levelStaffProjectAProjectB
higha245
highb1025
highc010

<tbody>
</tbody>

The table range (now 'data' table) was added to the Powerpivot model in Excel 2013. And the measures written as follows:

Count ProjectA:=CALCULATE(COUNTA(data[ProjectA]),data[level]="high")

Count ProjectB:=CALCULATE(COUNTA(data[ProjectB]),data[level]="high")

Sum ProjectA:=SUM([ProjectA])

Sum ProjectB:=SUM([ProjectB])












I want to count all rows. Distintcount works but when it comes down to sub total, if there is no value, it does not include the particual row.

Let's say: Project A for c person has not got a value. So when I use distintcount (level), it won't add C in Project A/Count in green. I need the High total for Project A/count in green should be 3 not 2. Regardless of the values, it just needs to count the total rows per level. Has anyone got an idea?

levelStaffProject A /valueProject B /valueProject A /countProject B/count
higha24511
b102511
c101
High Total128023

<tbody>
</tbody>
 
Last edited:
Upvote 0
SOQLEE, thanks for the reply. I did try replacing null with zero in measures and also in power pivot features. It didn't work. As you suggest I will clean the raw data first and use the measures. I will post back.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top