Forum Discussion
FILTER 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 occupation ( % ) = MAX(Time_management[Target_Occupation])
Capacity ( total ) = SUM(Time_management[Capacity])
When I use the same formula but without IF statement I have the following picture (Totals also wrong):
The question is how to show correctly Availability but not taking into account negative values and receive correct totals?
Thank you in advance!
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] )
15 Replies
Delphia , You have the force row total
return
sumx(values(Table[kentico]), if(_available>0, _available, blank()))
Thank 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!
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 ) )It works! Thank you so much for your detailed explanation and help!
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] )
