Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper V
3 years ago
Solved

Calulation of values within the same column

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_catJanFebMarchAprilMayJuneJulyAugSep
INT1000001200001558002856120000    
YTL    5000070000855007500080000
NB    10002000210015001890
FR    15001500150015001500

 

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_catJanFebMarchAprilMayJuneJulyAugSep
INT1000001200001558002856120000    
YTL    3000070000855007500080000
NB    10002000210015001890
FR    15001500150015001500


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

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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

2 Replies

  • M_SBS_6's avatar
    M_SBS_6
    Icon for Helper V rankHelper V
    3 years ago

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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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