Forum Discussion
COUNTROWS with ALL and FILTER
Hello everybody!
I have a table with the following structure linked to my data model:
| Item | Date | Value |
| A | 01.03.2020 | +550 |
| A | 02.03.2020 | -600 |
The Date Column is an active connection to the calendar table.
Then I have a Pivot table like this:
| Week | Value |
| 2020-01 | +5000 |
| 2020-01 | -50 |
This table shows me the sum for each week.
Now I want a second column in this pivot that shows me the total sum regardless of the week, but just for values above 500.
The following measure is not working:
=CALCULATE(COUNTROWS(tblOne);FILTER(tblOne;tblOne[Value]>500);ALL(tblOne))
=CALCULATE(COUNTROWS(tblOne);ALL(tblOne);FILTER(tblOne;tblOne[Value]>500))
How would you solve that?
Perhaps:
Measure = SUMX(FILTER(ALL('Table'),[Value]>500),[Value])Although I can't fathom why you were using COUNTROWS so if you actually want the count, use COUNTX instead of SUMX.
2 Replies
Perhaps:
Measure = SUMX(FILTER(ALL('Table'),[Value]>500),[Value])Although I can't fathom why you were using COUNTROWS so if you actually want the count, use COUNTX instead of SUMX.
