circledchicken
Well-known Member
- Joined
- Aug 13, 2011
- Messages
- 2,932
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:
<tbody>
</tbody>Excel 2010
I have two measures defined as follows:
<tbody>
</tbody>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.
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:
A | B | |
---|---|---|
1 | Product | Store |
2 | Apples | 1 |
3 | Oranges | |
4 | Pears | 1 |
5 | Apples | 1 |
6 | ||
7 | 2 | |
8 | Apples | 1 |
<tbody>
</tbody>
I have two measures defined as follows:
- Count of Stores:
=DISTINCTCOUNT(Table1[Store]) - UniqueCombinations:
=SUMX(DISTINCT(Table1[Product]), [Count of Stores])
A | B | |
---|---|---|
1 | Product | Store |
2 | Apples | 1 |
3 | Oranges | |
4 | Pears | 1 |
6 | ||
7 | 2 |
<tbody>
</tbody>
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.