Forum Discussion
Using CALCULATE when filter removes all data
I got the following example
| Provider | Project | Licitation | Order |
| 1 | A | X | 1 |
| 1 | A | - | 2 |
| 1 | B | Y | 3 |
| 2 | B | - | - |
| 2 | B | Z | - |
| 3 | A | - | 4 |
| 3 | C | - | 5 |
| 3 | C | - | 6 |
And i want a matrix/pivot table that counts for each provider how many projects/licitations/order has been involved in (not counting it therefore when a "-" appears).
The thing is that if I filter out directly these value, whenever a "-" appears for any column it will filter out all the row. Counting only the rows that contains value in all three columns (not even that because provider 3 should get all 0 based on that logic...):
| PROVIDER | PROJECTS | LICITATIONS | ORDERS |
| 1 | 2 | 2 | 2 |
| 2 | 1 | 1 | 1 |
| 3 | 1 | 1 | 1 |
Using a new measure using =CALCULATE for each column throws an error, because for example for provider 3 if you filter out "-" on licitation you filter out everything and gets empy.
How would I fix it?
The final result should look like:
| PROVIDER | # of PROJECTS | # of LICITATIONS | # of ORDERS |
| 1 | 2 | 2 | 3 |
| 2 | 1 | 1 | 0 |
| 3 | 2 | 0 | 3 |
jgtirz Maybe 3 measures:
# of PROJECTS = COUNTROWS('Table') + 0 # of LICITATIONS = COUNTROWS(FILTER('Table',[Licitation]<> "-")) + 0 # of ORDERS = COUNTROWS(FILTER('Table',[Order] <> "-")) + 0
2 Replies
jgtirz Maybe 3 measures:
# of PROJECTS = COUNTROWS('Table') + 0 # of LICITATIONS = COUNTROWS(FILTER('Table',[Licitation]<> "-")) + 0 # of ORDERS = COUNTROWS(FILTER('Table',[Order] <> "-")) + 0Cheers mate, that "+0" did the trick!
