Forum Discussion
FILTER only positive values
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 ) )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] )
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 )
)
Hm... Still have problem with Grand Totals per column... AlexisOlson
My matrix parameters are the following:
Thank you!
You have additional complications if the same name can appear under multiple skills. The skill level may be another level of granularity to take into consideration.
For things to necessarily add up, you need to sum the total at the same level of granularity as the individual cells. This may be easier with a slightly different approach:
Availability = VAR Summary = SUMMARIZE ( Staff, Staff[Skill], Staff[Experience level], Staff[Name], "@Hours", [Target Capacity] - [Planned Hours ( Total )] ) RETURN SUMX ( FILTER ( Summary, [@Hours] > 0 ), [@Hours] )It doesn't show correct values...
The problem is that I don't have names in the table Skills_DB, only staffer_id. Names are in table Staffer.
If I apply your formula to matrix, it works with staffer_id. But I actualy need names...
My schema is the following:
How about this?
Availability = VAR Summary = SUMMARIZE ( Staff, Staff[Name], Skills_DB[Skill], Skills_DB[Experience level], "@Hours", [Target Capacity] - [Planned Hours ( Total )] ) RETURN SUMX ( FILTER ( Summary, [@Hours] > 0 ), [@Hours] )