FILTER only positive values

[Delphia]

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 occupation ( % )]*[Capacity ( total )]
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!

[AlexisOlson · Accepted solution]

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 )
)

[AlexisOlson · Accepted solution]

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 )
)

[Delphia]

Hm... Still have problem with Grand Totals per column... @AlexisOlson 

My matrix parameters are the following:

Thank you!

[AlexisOlson]

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] )

[Delphia]

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:

[AlexisOlson]

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] )

[Delphia]

Unfortunately, no...

[AlexisOlson]

I 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.

[Delphia]

Sorry, 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 · Accepted solution]

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]

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!

[Delphia]

Thank you so much for your help!

[Delphia]

It works! Thank you so much for your detailed explanation and help!

[amitchandak]

@Delphia , You have the force row total 

return

sumx(values(Table[kentico]), if(_available>0, _available, blank()))

[Delphia]

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!