Forum Discussion
Create Measure to calculate Net impact on filtered value
Hello All
I have not been able to find a solution for this. I have a large dataset, the very top level is ProgramID. I want to be able to filter on the Total NET impact of the ProgramID regardless of how many lines there are. The final intent would be to filter out any NET impact <0 on several other metrics.
TIA
Sample of data below.
PJ-2 is NET 296 so it would stay.
PJ-3 is NET -9 so it would filter out.
| ProgramID | Application | Total Impact |
| PJ-1 | AAAA | 168 |
| PJ-1 | BBBB | 1,950 |
| PJ-2 | AAAA | 804 |
| PJ-2 | BBBB | (4) |
| PJ-2 | CCCC | (504) |
| PJ-3 | AAAA | (4) |
| PJ-3 | BBBB | (2) |
| PJ-3 | CCCC | (2) |
| PJ-4 | AAAA | 21 |
Hi Jodes007 ,
Sorry, not very clear.
If you want to filter out rows NET impact<0 in the visual. In the Filters, set up as follows.
If you want to filter out NET impact<0 in the measure.
COUNT TOTAL = CALCULATE(COUNT('Table'[ProgramID]),FILTER(ALLSELECTED('Table'),[Total Impact]>=0))COUNT GROUP BY ProgramID = CALCULATE(COUNT('Table'[ProgramID]),FILTER(ALLSELECTED('Table'),[ProgramID]=MAX('Table'[ProgramID])&&[Total Impact]>=0))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Jodes007 ,
Sorry, not very clear.
If you want to filter out rows NET impact<0 in the visual. In the Filters, set up as follows.
If you want to filter out NET impact<0 in the measure.
COUNT TOTAL = CALCULATE(COUNT('Table'[ProgramID]),FILTER(ALLSELECTED('Table'),[Total Impact]>=0))COUNT GROUP BY ProgramID = CALCULATE(COUNT('Table'[ProgramID]),FILTER(ALLSELECTED('Table'),[ProgramID]=MAX('Table'[ProgramID])&&[Total Impact]>=0))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry, it should have read NET impact >0. Filtering does not work as the Total impact is at the Application level where there are many lines. BUT the count group looks like it makes sense. I will try it today and see.
Hi Jodes007 ,
Is your problem solved?
If so, please accept the reply as solution. More people will benefit.
If not, please provide me with more details about it.
Best Regards,
Stephen Tao
