Forum Discussion
Advocate IIFILTER 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] )
Super UserI can't easily debug name errors without access to the file. I can't even tell if your table is Skills_DB or Skills since you have screenshots with both variations.
Advocate IISorry, it's the same table, I just renamed it. If I understand correctly the error, the problem is that in SUMMARIZE function in your formula the table that is used is Staff (the first parameter), but then you use columns from another table.
- AlexisOlson
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
Thank you so much for your help!
- Delphia
One 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
I solved this, thank you once more time!