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] )
Delphia
Advocate II
5 years agoOne more qustion. If I want to apply specific coefficient to this measure, is it any way to do it inside calculation?
I need to multiply the Availability by Skills[Factor] that depends on:
1. Staff[Name] (or Skills[staffer_id])
2. Skills[Skill]
Thank you in advance!
Delphia
Advocate II
5 years agoI solved this, thank you once more time!