Need help please to sum and filter based on different value criteria from the same column

[DanPell]

Hi there

I'm having trouble summing values based on values contained in another column.  

I would like to sum store Campaign Sales Targets based on Campaign Names to get a Group number.    The only thing is I have some stores in Northern Hemisphere and some in Southern Hemisphere so a Spring Sale in South Hem = Autumn Sale in North Hem

I have a Campaign Name slicer on my Dashboard 

One column has Campaign Name (Spring Sale, Autumn Sale etc) and I would like to sum Campaign Sales targets (another column) i.e.  

if (Selected Value) 'Table' [Campaign Name] = "Spring Sale"

Sales Targets_AU + Sales Targets_NZ + 

calculate(

Sales Targets_USA, 

'Table' [Campaign Name] = "Autumn Sale"

The Sales Targets_USA needs to be filtered to sum targets for "Autumn Sale" but it seems to be summing Spring Sale numbers.

i.e, the filter for "Autumn Sale" doesn't appear to be working)

Are you able to assist please? Thanks!

[Anonymous · Accepted solution]

Hi @DanPell ,

I see. I assume the user is in the southern hemisphere to observe the seasons. You can hard-code the country code into the measure:

MEASURE = 
VAR __N_countries = { "US" }
VAR __curr_selected_value =
    SELECTEDVALUE ( 'Table'[Campaign Name] )
VAR __result =
    SWITCH (
        TRUE (),
        ISBLANK ( __curr_selected_value ), SUM ( 'Table'[Campaign Targets] ),
        __curr_selected_value = "Spring Sale",
            CALCULATE (
                SUM ( 'Table'[Campaign Targets] ),
                NOT 'Table'[Country Code] IN __N_countries
            )
                + CALCULATE (
                    SUM ( 'Table'[Campaign Targets] ),
                    'Table'[Campaign Name] = "Autumn Sale",
                    'Table'[Country Code] IN __N_countries
                ),
        __curr_selected_value = "Autumn Sale",
        CALCULATE (
                SUM ( 'Table'[Campaign Targets] ),
                NOT 'Table'[Country Code] IN __N_countries
            )
                + CALCULATE (
                    SUM ( 'Table'[Campaign Targets] ),
                    'Table'[Campaign Name] = "Spring Sale",
                    'Table'[Country Code] IN __N_countries
                )
    )
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Hi @DanPell ,

Please create a new table for the slicer without connection:

DimCampaignName = DISTINCT('Table'[Campaign Name])

then create a new measure:

MEASURE = 
VAR __curr_selected_values =
    ALLSELECTED ( 'DimCampaignName'[Campaign Name] )
VAR __result =
    SWITCH (
        TRUE (),
        COUNTROWS ( __curr_selected_values )
            = COUNTROWS ( ALL ( 'DimCampaignName'[Campaign Name] ) ),
            SUM ( 'Table'[Sales Targets_AU] ) + SUM ( 'Table'[Sales Targets_NZ] )
                + SUM ( 'Table'[Sales Targets_USA] ),
        SUM ( 'Table'[Sales Targets_AU] ) + SUM ( 'Table'[Sales Targets_NZ] )
            + CALCULATE (
                SUM ( 'Table'[Sales Targets_USA] ),
                NOT 'Table'[Campaign Name] IN __curr_selected_values
            )
    )
RETURN
    __result

 output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[DanPell]

Hi @Anonymous thanks for that.  I will give it a try but as i mentioned there is already a slicer for Campaign Name on my dashboard that I would like to have the Campaign Targets linked towards.  Is that possible based on your solution.  Also the data is arranged as follows in the dataset (not sure if it helps):

Campaign Name      Store Name         Campaign Targets      Country Code

Spring Sale                ABC                            100                         AU

Spring Sale                XYZ                             250                         NZ 

Autumn Sale              XYZ                             275                         NZ

Autumn Sale              ABC                            125                         AU

Spring Sale                 CDE                             375                        US

Autumn Sale               CDE                             425                        US

Based on the above I have created measures for Campaign Targets based on Country  (Sales Targets_AU etc) and when I want to add the the Campaign targets measures I encounter the issue where for example the US Spring Sale Targets add to AU and NZ Spring Sale when it should be adding US Autumn Sale  Does your solution allow for this dataset layout?  Thanks! 

[Anonymous · Accepted solution]

Hi @DanPell ,

I see. I assume the user is in the southern hemisphere to observe the seasons. You can hard-code the country code into the measure:

MEASURE = 
VAR __N_countries = { "US" }
VAR __curr_selected_value =
    SELECTEDVALUE ( 'Table'[Campaign Name] )
VAR __result =
    SWITCH (
        TRUE (),
        ISBLANK ( __curr_selected_value ), SUM ( 'Table'[Campaign Targets] ),
        __curr_selected_value = "Spring Sale",
            CALCULATE (
                SUM ( 'Table'[Campaign Targets] ),
                NOT 'Table'[Country Code] IN __N_countries
            )
                + CALCULATE (
                    SUM ( 'Table'[Campaign Targets] ),
                    'Table'[Campaign Name] = "Autumn Sale",
                    'Table'[Country Code] IN __N_countries
                ),
        __curr_selected_value = "Autumn Sale",
        CALCULATE (
                SUM ( 'Table'[Campaign Targets] ),
                NOT 'Table'[Country Code] IN __N_countries
            )
                + CALCULATE (
                    SUM ( 'Table'[Campaign Targets] ),
                    'Table'[Campaign Name] = "Spring Sale",
                    'Table'[Country Code] IN __N_countries
                )
    )
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[DanPell]

Thanks this worked.

[DanPell · Accepted solution]

Hey thanks for this, awesome.  Just a quick one if I have multiple Northern Hem countries (i.e. US, CA, UK,) is that as easy as putting the respective country codes into the VAR measure i.e.

VAR__N_Countries = {"US","CA","UK"}??

Thanks!

[Kedar_Pande]

@DanPell 

Create a Measure:

CampaignSalesTargets =
VAR SelectedCampaign =
SELECTEDVALUE ( 'Table'[Campaign Name] )
RETURN
SWITCH (
TRUE (),
SelectedCampaign = "Spring Sale",
SUM ( 'Table'[Sales Targets_AU] ) + SUM ( 'Table'[Sales Targets_NZ] )
+ CALCULATE (
SUM ( 'Table'[Sales Targets_USA] ),
'Table'[Campaign Name] = "Autumn Sale"
),
SelectedCampaign = "Autumn Sale",
SUM ( 'Table'[Sales Targets_AU] ) + SUM ( 'Table'[Sales Targets_NZ] )
+ CALCULATE (
SUM ( 'Table'[Sales Targets_USA] ),
'Table'[Campaign Name] = "Spring Sale"
),
BLANK () // Default case if no campaign is selected
)

This should resolve your filtering issue and provide accurate results in your dashboard.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[DanPell]

Thanks mate, I have resolved the problem.

[Ritaf1983]

Hi @DanPell 

Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[DanPell]

Thaks Rita - I have worked it out!