Filter and Sum

[Bandana_Havana9]

Hi, 

I need add together the items sold based on specified sales code.  This is what I currently have, but it is not working. Do I need to create a measure for grouping the sales code first (1000, 2000, etc). And then create a measure for the adding the SUM of the specified sales code. 

Hopefully this makes sense. Let me know if you have any questions. 

Thanks!

Total Sold Grouped =

CALCULATE(

    SUM(TABLE 1[PRICE]),

    FILTER(

        SAPOSTED,

        VALUE(RELATED(TABLE 2[SALES CODE])) IN {1000,2000,3000,4000,5000}

    )

[v-dineshya]

Hi @Bandana_Havana9 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @KNP  and @Ahmed-Elfeel , Thank you for your prompt responses.

Hi @Bandana_Havana9 ,  Could you please try the proposed solutions shared by @KNP  and @Ahmed-Elfeel  ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[v-dineshya]

Hi @Bandana_Havana9 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @Bandana_Havana9 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[Ahmed-Elfeel]

Hi @Bandana_Havana9,

Your approach is on the right track, but there are a few potential issues with the DAX formula Like:

• You are using TABLE 1[PRICE] but filtering SAPOSTED
• VALUE function might not be necessary if SALES CODE is already a numeric column
• Relationship requirement - This assumes a proper relationship between the tables

So I have 3 Solutions for you the 1st approach is (If you have a relationship between tables) :

Total Sold Grouped =
CALCULATE(
    SUM('TABLE 1'[PRICE]),
    'TABLE 2'[SALES CODE] IN {1000,2000,3000,4000,5000}
)

The 2nd approach Using FILTER (if no direct relationship):

Total Sold Grouped =
CALCULATE(
    SUM('TABLE 1'[PRICE]),
    FILTER(
        'TABLE 2',
        'TABLE 2'[SALES CODE] IN {1000,2000,3000,4000,5000}
    )
)

Finally the Last approach Using TREATAS (alternative approach):

Total Sold Grouped =
CALCULATE(
    SUM('TABLE 1'[PRICE]),
    TREATAS({1000,2000,3000,4000,5000}, 'TABLE 2'[SALES CODE])
)

So to answer your questions:

1. Do you need to create a measure for grouping sales codes first?

   • No you can directly filter them in the CALCULATE function as shown above
2. Do you need a measure for adding the SUM?
   • The measures I provided already handle both the filtering and summing

Note:Use Option 1 if you have a proper relationship between TABLE 1 and TABLE 2. Use Option 2 if you don't have a direct relationship.

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[KNP · Accepted solution]

It's difficult to be sure without seeing your model and relationships, but, I would think this syntax should work.

Total Black = 
    CALCULATE(
        SUM(FactInternetSales[SalesAmount])
        , FILTER(DimProduct, DimProduct[Color] IN {"Black"})
    )