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] )
AlexisOlson
Super User
5 years agoSuppose 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 )
)Delphia
Advocate II
5 years agoIt works! Thank you so much for your detailed explanation and help!