SUM of total with filter condition

[Anonymous]

Hi All,

Please see below data set,As per my reuirements if REGION DESC=”Europe” and Class=”Part”,”Hardware”,”Software” I want to sum of impact for Part+hardware+software (100+200+300=600) and if REGION DESC=”ASIA” and Class=”Part”,”Hardware”,”Software” I want to sum of impact for Part+hardware+software (100+200+300=600)…so on.

 I have used if and switch but sum does not filter by region desc and class.

Can any one has any suggeston?

thanks

Region Desc	Class	Impact
Europe	PART	100
Europe	Hardware	200
Europe	software	300
Europe	SERVICE	400
Asia	SERVICE	500
Asia	PART	600
Asia	Hardware	700
Asia	software	800

[Ashish_Mathur]

Hi,

Based on the data that you have shared, please share the exact result.  For Asia the numbers are not 100,200 and 300.  So i am confused.  Also, do you want a measure or a calculated column formula?

[Anonymous]

Hi,

I am really sorry for typo.

For Region=asia,classs=service,part,software and sum of impact=500+600+800=1900.

From my dataset I can say calculated perform well.

Let me know if you have any suggestion.

Thanks

[Ashish_Mathur]

Hi,

So you want a calculated column formula.  Share the exact result you are expecting in a spare column of the Table that you shared i your original post.

[Anonymous]

Hi Ashish,

Please see below,

My expected result,

Region	Total Impact
Europe	600
Asia	1900

1. Total Impact is a new column ,

>Total Impact For Region=asia,classs=service,part,software and sum of impact=500+600+800=1900.

>Total Impact REGION DESC=”Europe” and Class=”Part”,”Hardware”,”Software” I want to sum of impact for Part+hardware+software (100+200+300=600)

Please see my approch and suggest me any changes,with below steps I am getting right result but I need to created at least 50 column and after that I am going to use switch.Please suggested me any efficient way.

A.I have created tow columns,one for europe and one for asia.

First column:asia =
IF (
    CALCULATE (
        SUM ( TABLE_5[IMPACT] ),
        FILTER (
            TABLE_5,
            [Region Desc] = "ASIA"
        ),
        FILTER (
            TABLE_5,
            [Class] = "ACCESSORY"
                || [Class] = "CONSUMABLE"
                || [Class] = "SOFTWARE"
        )

Second Column:Europe=delete for me 2 =
IF (
    CALCULATE (
        SUM ( TABLE_5[IMPACT] ),
        FILTER (
            TABLE_5,
            [Region Desc] = "Europe"
        ),
        FILTER (
            TABLE_5,
            [Class] = "ACCESSORY"
                || [Class] = "CONSUMABLE"
                || [Class] = "SOFTWARE"
        )

B.I have used switch,

Total Impact=switch(true(),region desc=europe,europe,

                                                 Region desc=asia,asia),0)

[Ashish_Mathur · Accepted solution]

Hi,

Drag Region to the Table visual and Class to the filter section.  In the Filter, select your desired classes.  Write this measure

=SUM(Data[Value])

Hope this helps.

[chotu27]

@Anonymous

Create a conditional column from EditQueries option like below image.

[Anonymous]

Hi Chotu,

In conditional column "THEN"wont allow me to SUM(IMPACT) and region and desc is not correctly populated data.

thanks

[Anonymous]

Hello Eveyone,

I have used below formula but did not get correct result.

Is there any suggestion?

delete for me 2 =
IF (
    CALCULATE (
        SUM ( TABLE_5[IMPACT] ),
        FILTER (
            TABLE_5,
            [Region Desc] = "ASIA"
        ),
        FILTER (
            TABLE_5,
            [Class] = "ACCESSORY"
                || [Class] = "CONSUMABLE"
                || [Class] = "SOFTWARE"
        )
    ),
    (
        CALCULATE (
            SUM ( TABLE_5[IMPACT] ),
            FILTER (
                TABLE_5,
                [Region Desc] = "EUROPE"
            ),
            FILTER (
                TABLE_5,
                [Class] = "ACCESSORY"
                    || [Class] = "CONSUMABLE"
                    || [Class] = "SOFTWARE"
            )
        )
    )
)