Forum Discussion
Delphia
Advocate II
5 years agoFILTER only positive values
Hi everybody, I have a problem with filtering only positive values. I have the following formula (see below). When I use it in Matrix, it shows wrong Totals. Target Capacity = [Target ...
- 5 years ago
Suppose the lowest level of your hierarchy in your visual is Table1[Item]. Then you need to iterate over each of those items.
Availability ( hours + ) = SUMX ( VALUES ( Table1[Item] ), VAR __available = [Target Capacity] - [Planned Hours ( Total )] RETURN IF ( __available > 0, __available ) ) - 5 years ago
You can use other columns from related tables. See the documentation.
It might work better to start with Skills and use Staff[Name] though.
Availability = VAR Summary = SUMMARIZE ( Skills, Staff[Name], Skills[Skill], Skills[Experience level], "@Hours", [Target Capacity] - [Planned Hours ( Total )] ) RETURN SUMX ( FILTER ( Summary, [@Hours] > 0 ), [@Hours] )
amitchandak
Super User
5 years agoDelphia , You have the force row total
return
sumx(values(Table[kentico]), if(_available>0, _available, blank()))
Delphia
Advocate II
5 years agoThank you. It helps to eliminate negative values but totals are still not correct...
As the result it should be:
Total for column Basic: 56+24=80,
Total for column Intermediate: 16
Total for Kentico: 80+16=96
Thank you!