SUMX: Count unique records in a table (ignoring blanks blank rows)

[circledchicken]

Hi PowerPivot experts,

I am beginning to learn how to use PowerPivot and this question is based on the last 'Useful' example in this post:
SUMX() – The 5-point palm, exploding fxn technique « PowerPivotPro

I have a table named Table1 that looks like this:

[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Product[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Pears[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

I have two measures defined as follows:

• Count of Stores:
  =DISTINCTCOUNT(Table1[Store])
• UniqueCombinations:
  =SUMX(DISTINCT(Table1[Product]), [Count of Stores])

In my PivotTable, the only element is the 'UniqueCombinations' measure in the Values section. Currently the value of the measure is 5 corresponding to these rows:

[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Product[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Pears[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
My question is, how do I adjust the measures to exclude any rows where all the corresponding columns are blank? I would like my UniqueCombinations measure to return 4 in this case (excluding row 6).

I know I could create a calculated column for example and then count non-blanks on that (using something like =COUNTAX( DISTINCT( Table1[CalcColumn] ), Table1[CalcColumn] ). However I would like to learn how to do this using purely measures if possible.

Many thanks for your time.

 

[miguel.escobar]

=CALCULATE(DISTINCTCOUNT(Table1[Product]), FILTER(Table1,[Product] <> BLANK()))

The formula above its basically saying that I want to have a distinct count of the items in column name Product and then there's a filter that states that I don't want to count the blanks, just the ones that are non-blank..

Below my Example table:
[TABLE="width: 134"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps.

Note: there are other ways of calculating this but that's the first solution that I could come up with.

Best!
Miguel

 

[miguel.escobar]

My bad...I needed to re-read what you were actually aiming at. The correct formula is:
=CALCULATE(DISTINCTCOUNT([Product]),FILTER(Table1,
COUNTA([Store]) <> 0 && COUNTA([Product]) <> 0)
)


It's saying that I'd like to calculate a distinct count of the column [Product] but first filtering the table and only counting the records that are not completely blank hence the
COUNTA([Store]) <> 0 && COUNTA([Product]) <> 0

This should do the job.

Best!
Miguel

 

[miguel.escobar]

MAN! you really had me thinking on this one! =D, I really enjoyed this one.

I'm working on the final cut

 

[miguel.escobar]

=IF(CALCULATE(COUNTBLANK([Product]),FILTER(Table1,[Store]<>BLANK()))>=1,1,0)+ CALCULATE(DISTINCTCOUNT([Product]),FILTER(Table1,[Product]<>BLANK()))

(blanks on product but that are not blanks on store + nonblanks on product)

take in consideration that once the:
blanks on product but that are not blanks on store

case appears, I use the IF to just sum 1 and not count all of those cases.

Miguel

 

[circledchicken]

=IF(CALCULATE(COUNTBLANK([Product]),FILTER(Table1,[Store]<>BLANK()))>=1,1,0)+ CALCULATE(DISTINCTCOUNT([Product]),FILTER(Table1,[Product]<>BLANK()))

(blanks on product but that are not blanks on store + nonblanks on product)

take in consideration that once the:
blanks on product but that are not blanks on store

case appears, I use the IF to just sum 1 and not count all of those cases.

Miguel

Hi Miguel

Thankyou for your help with this, much appreciated (and I'm glad you enjoyed it )!

I need to learn more about how CALCULATE works, but I don't think that last version is completely correct, because for a case like this:

[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Product[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Pears[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

It would return 4, but I need it to return 5 corresponding to the following:

[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Product[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Pears[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[miguel.escobar]

At last! I finally get it.

You want to have a distinct count of the actual combination of column A(Product) and Column B(Store).
=COUNTROWS(SUMMARIZE(Table1,[Product],[Store],"String", CONCATENATE(Table1[Product],Table1[Store])))-IF(AND(COUNTBLANK([Store])>=1,COUNTBLANK([Product])>=1),1,0)

That's quite easy actually. The approach that I like to use is the SUMMARIZE one which is basically creating a select query within my tables to give me another tables that looks like this

Product (all grouped) - Store (All grouped) - Concatenation of both

what we mean by grouped it's that its only showing the distinct labels for those instance.
Once we actually have the SUMMARIZE thing going on, all we have to do it's have a COUNTROWS of those and since you don't want to count the instances where both columns are blank that's why I added the subtraction with
IF(AND(COUNTBLANK([Store])>=1,COUNTBLANK([Product])>=1),1,0)

where, in the event that there's an instance where both columns are blank, it will subtract 1 from the count of the total rows in the summarize.

Let me know if this formula works for you.

Miguel

 

[circledchicken]

Hi Miguel,

Thanks again for having a look. Unfortunately, it still doesn't work quite as expected.

For example, with the table below the measure should return 5 (rows 2,3,4,6 and 7), but instead it returns 4:

[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Product[/TD]
[TD]Store[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Oranges[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Pears[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Apples[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

I only asked this question because I'm beginning to learn how to use PowerPivot - I have no real need for a solution at the moment so please feel free to ignore it if you want!

Thanks for all the help you have offered so far though, much appreciated.

 

[miguel.escobar]

it's personal now...this formula won't beat me
The summarize function is doing the job but the other part of the formula is not and its because we don't wanna count the blank+blank combination.

 

[AlbertoFerrari]

Hi eveybody, would you like to try out this formula?

=SUMX (
    VALUES (Table1[Product]),
    IF (
        ISBLANK ( Table1[Product] ),
        CALCULATE (
            DISTINCTCOUNT ( Table1[Store] ),
            NOT ( ISBLANK ( Table1[Store] ))
        ),
        CALCULATE (
            DISTINCTCOUNT ( Table1[Store] )
        )
    )
)

Seems to compute the right value, and it is pretty easy to understand, even if it uses the CALCULATE inside SUMX, which is the only tricky part.

Alberto