Calulation of values within the same column

[M_SBS_6]

Hi,

I have the following calculation that provides me with the majority of the output required and can be seen within the table below.

SUMX(

apps,

IF('apps'[app_cat] in {"INT","YTL","NB"} && apps[curr_month_or_after] = "No", "",

IF('apps'[app_cat] in {"INT"} && apps[curr_month_or_after] = "No" ||

'apps'[app_cat] = "INT" && 'apps'[app_type] = 1,

('apps'[amount] * apps[app_curr]) / 100,

'apps'[amount]))

)

app_cat	Jan	Feb	March	April	May	June	July	Aug	Sep
INT	100000	120000	155800	28561	20000
YTL					50000	70000	85500	75000	80000
NB					1000	2000	2100	1500	1890
FR					1500	1500	1500	1500	1500

However, what I need to do is where curr_month = "Yes" (i.e May) then I need a calculation that will say somthing like this:

SUM(IF('apps'[amount], 'apps'[app_cat] = "YTL" && apps[curr_month] = "Yes"

-  

'apps'[app_cat] = "INT" && apps[curr_month] = "Yes"

of which I have attempted to do here in red but the values don't change:

SUMX(
apps,

(if(apps[curr_month] = "Yes" && 'apps'[app_cat] = "YTL", 'apps'[amount]
-
if(apps[curr_month] = "Yes" && 'apps'[app_cat] = "INT", 'apps'[amount]),

IF('apps'[app_cat] in {"INT","YTL","NB"} && apps[curr_month_or_after] = "No", "",

IF('apps'[app_cat] in {"INT"} && apps[curr_month_or_after] = "No" ||

'apps'[app_cat] = "INT" && 'apps'[app_type] = 1,

('apps'[amount] * apps[app_curr]) / 100,

'apps'[amount]))

)

Then my table output should show like this (changes to the values in RED). So the value in May for app_cat "YTL" has a reduced value by 20000 as that's that value in app_cat "INT"

app_cat	Jan	Feb	March	April	May	June	July	Aug	Sep
INT	100000	120000	155800	28561	20000
YTL					30000	70000	85500	75000	80000
NB					1000	2000	2100	1500	1890
FR					1500	1500	1500	1500	1500

Any idea how I could look to add in this logic to my existing please? 

[v-yanjiang-msft · Accepted solution]

Hi @M_SBS_6 ,

According to your description, here's my solution.

SUMX (
    apps,
    IF (
        apps[curr_month] = "Yes"
            && 'apps'[app_cat] = "INT",
        'apps'[amount],
        IF (
            apps[curr_month] = "Yes"
                && 'apps'[app_cat] = "INT",
            'apps'[amount]
                - SUMX (
                    FILTER ( ALL ( apps ), apps[curr_month] = "Yes" && 'apps'[app_cat] = "INT" ),
                    'apps'[amount]
                ),
            IF (
                'apps'[app_cat]
                    IN { "INT", "YTL", "NB" }
                    && apps[curr_month_or_after] = "No",
                "",
                IF (
                    'apps'[app_cat]
                        IN { "INT" }
                        && apps[curr_month_or_after] = "No"
                        || 'apps'[app_cat] = "INT"
                        && 'apps'[app_type] = 1,
                    ( 'apps'[amount] * apps[app_curr] ) / 100,
                    'apps'[amount]
                )
            )
        )
    )
)

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

Best regards,

Community Support Team_yanjiang

[M_SBS_6]

I don't suppose anyone has a suggestion at all please? I really don't know where to go with this.