Helper I

1 year ago

Solved

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

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!

Need Help

Tips and Tricks

Anonymous

1 year ago

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

demo.pbix22 KB

DanPell
1 year ago
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!

9 Replies

Ritaf1983
Super User
1 year ago
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-Forum/ba-p/963216
- DanPell
  Helper I
  1 year ago
  Thaks Rita - I have worked it out!

Kedar_Pande

Super User

1 year ago

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
Helper I
1 year ago
Thanks mate, I have resolved the problem.

Anonymous
Not applicable
1 year ago
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
demo.pbix23 KB
- DanPell
  Helper I
  1 year ago
  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
    Not applicable
    1 year ago
    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
    demo.pbix22 KB

Forum Discussion

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

9 Replies

Related content

Date difference with multiple criteria

Different RLS criterias on different sheets

Sequential Order column based on multiple criteria

Help with date subtraction with some criteria from different rows

Help Please