Count distinct rows for sub total

[agasi]

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?

[TABLE="width: 500"]
<tbody>[TR]
[TD]level[/TD]
[TD]Staff[/TD]
[TD]Project A /value[/TD]
[TD]Project B /value[/TD]
[TD]Project A /count[/TD]
[TD]Project B/count [/TD]
[/TR]
[TR]
[TD]high[/TD]
[TD]a[/TD]
[TD]2[/TD]
[TD]45[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]b[/TD]
[TD]10[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]High Total[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]80[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[SOQLee]

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.


[TABLE="width: 244"]
<tbody>[TR]
[TD="class: xl64, width: 61"]level[/TD]
[TD="class: xl64, width: 61"]Staff[/TD]
[TD="class: xl64, width: 61"]ProjectA[/TD]
[TD="class: xl64, width: 61"]ProjectB[/TD]
[/TR]
[TR]
[TD="class: xl65"]high[/TD]
[TD="class: xl65"]a[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]45[/TD]
[/TR]
[TR]
[TD="class: xl66"]high[/TD]
[TD="class: xl65"]b[/TD]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl65, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl66"]high[/TD]
[TD="class: xl65"]c[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl65, align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

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?

[TABLE="width: 500"]
<tbody>[TR]
[TD]level[/TD]
[TD]Staff[/TD]
[TD]Project A /value[/TD]
[TD]Project B /value[/TD]
[TD]Project A /count[/TD]
[TD]Project B/count[/TD]
[/TR]
[TR]
[TD]high[/TD]
[TD]a[/TD]
[TD]2[/TD]
[TD]45[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]b[/TD]
[TD]10[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]c[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]High Total[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]80[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[agasi]

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.