add filter to measure formula

[GhadaMehrez]

Please I need support here I have measure calculating percentage of {Total estimated cost} per each strategy category 

but now I need to add filter to the measure to give me the percentage also based on period (Short Term- Mid Term- Long Term)

% ETC V3 = 
DIVIDE(
    [ETC This Year],
    CALCULATE(
        [ETC This Year],
        ALL('Projects'[fls_StrategyThemeName])
    ) 
)

I created another measure calculating N0. of months between start and end date of projects so I can get the period.

No.of months = SUMX(Projects, DATEDIFF(Projects[fls_actualstartdate],Projects[fls_actualenddate],month))

I tried to add this filter to the measure, for the product development I, but it gives wrong percentage:

% ETC V3 = 
DIVIDE(
    [ETC This Year],
    CALCULATE(
        [ETC This Year],
        ALL('Projects'[fls_StrategyThemeName]),FILTER(Projects, [No.of months] <=12)
    ) 
)

 

[v-chenwuz-msft]

Hi @GhadaMehrez ,

Please try this:

% ETC V3 =
DIVIDE (
    [ETC This Year],
    CALCULATE (
        [ETC This Year],
        FILTER ( ALLSELECTED ( Projects ), [No.of months] <= 12 )
    )
)

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GhadaMehrez]

@v-chenwuz-msft  Thanks for your support,

But the output of the measure is wrong it gives me 132% as the screen below:

I tried to update the measure to rely on another measure {Duration Flag}

but it gives error could you support :

% AB = DIVIDE ( [ETC This Year], CALCULATE ( [ETC This Year], FILTER ( ALLSELECTED ( Projects ), [Duration Flag] Short Term ) ) )

[v-chenwuz-msft]

Hi @GhadaMehrez ,

This:

%AB =
VAR _all =
    CALCULATE (
        [ETC This Year],
        FILTER ( ALLSELECTED ( Projects ), [no.month] <= 12 )
    )
VAR _s =
    SUMMARIZE (
        Projects,
        [fls_strategythemename],
        [pum_name],
        "etc", [etc this year],
        "ab", DIVIDE ( [etc this year], _all )
    )
RETURN
    SUMX ( _s, [ab] )

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GhadaMehrez]

thanks a lot for your support but still the output not correct it give me above 100% 

I'm attaching link of simplified PBI i hope if you can access it and help.

Thanks in advance.

https://drive.google.com/file/d/1emP0cQwYm9OHvqQrveVkwR69AH5TzG1S/view?usp=sharing

[amitchandak]

@GhadaMehrez , Try like

% ETC V3 =
DIVIDE(
[ETC This Year],
CALCULATE(
Sumx(FILTER(Projects, [No.of months] <=12),[ETC This Year]),
ALL('Projects'[fls_StrategyThemeName]),
)
)

or

% ETC V3 =
DIVIDE(
Sumx(FILTER(Projects, [No.of months] <=12),[ETC This Year]),
CALCULATE(
Sumx(FILTER(Projects, [No.of months] <=12),[ETC This Year]),
ALL('Projects'[fls_StrategyThemeName]),
)
)

[GhadaMehrez]

Hi @amitchandak , I tried to change a little in the expression to rely directly on the Actual Start and end dates not on the measure (No. Month) but still not working any idea how to fulfill this need please 

% AC = 
DIVIDE(
[ETC This Year],
CALCULATE(
Sumx(FILTER(Projects, DATEDIFF(Projects[fls_actualstartdate],Projects[fls_actualenddate],MONTH <=12, ALL(Projects[fls_StrategyThemeName])
))

[GhadaMehrez]

please your support Im totally lost in applying this but I'm thinking if I make it more simple and add direct custom filter to set duration between start and end date, Is it possible?

[GhadaMehrez]

not works:

[GhadaMehrez]

@amitchandakI hope if you can  support in this