cancel
Showing results for
Did you mean:
Helper III

## SUMX with 2 criterias - is it possible?

Dear all,

I'm struggling with the formula SUMX.  I need to calculate the sum of 2 different data, but with SUMX, only 1 value is allowed.  Is it possible to apply 2 values with SUMX or other DAX formulas?  For Example:

Customer / Item / Sales

A  / 111 / 100€

B  / 222/ 200€

C / 111 / 150€

D  / 333 / 300€

E  / 222 / 250€

A  / 333 / 400€

If the criteria/lever is "Customer", the formula/measure will be = SUMX(VALUES(Table[Customer], [Sales]); if the criteria is "Item", the formula will be = SUMX(VALUES(Table[Item], [Sales]).

How can I combine both criterias "Customer" & "Item" into 1 without duplicates?

Best regards

14 REPLIES 14
Community Support

Hi @EZV12 ,

Would you please why you use SAMEPERIODLASTYEAR in the formula?

If you want to use time intellgence function you'd better have a dim_date table, and in your pbix , there no filter on the date column ,the SAMEPERIODLASTYEAR function will not work  as your expected.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Helper III

Hello,

Thanks for your reply, however, what I would like to figure out is how to get a combined data (Customer & Item) for "Sum Benefit from Selling Price".

Can you help?

Many thanks.

Community Support

Hi @EZV12 ,

Create a new table :

``Table = UNION(SELECTCOLUMNS(Customer,"Category","Customer","Value",Customer[Customer]),SELECTCOLUMNS('Item',"Category","Item","Value2",'Item'[Item]))``

Then use the following measure:

``test = IF(MAX('Table'[Category]) ="Customer", CALCULATE([Sum Benefit from SP Customer],TREATAS(VALUES('Table'[Value]),Customer[Customer])), CALCULATE([Sum Benefit from SP item],TREATAS(VALUES('Table'[Value]),'Item'[Item])))``

For more details, please refer to the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Helper III

Hello Dedmon,

Thanks a lot for your help, however, it is not a filter I need.  When you put the filter, the data is for "Customer" and "Item" respectively, what I need is to have a combined calculation of "Sum benefit from selling price" for consolidated "Customer & Item".  Is it possible to have the formula "Sumx" based on 2 values, namely "Customer & Item" at the same time?

Sorry to bother you, but I'm struggling a lot to solve this issue.  If you may find a solution, it would be great!

Community Support

Hi @EZV12 ,

Best Regards,

Dedmon Dai

Community Support

Hi @EZV12 ,

You can try to use the following measure:

``Measure = SWITCH(TRUE(),ISFILTERED(Table1[Customer]), SUMX(VALUES(Table1[Customer]), [Sales]),ISFILTERED(Table2[Item]), SUMX(VALUES(Table2[Item]), [Sales]))``

If it doesn't meet your requirements, please show us sample pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Helper III

### Hello @v-deddai1-msft ,

Thank you very much for your reply. However, I'm confused. Here is the sample pbix file.

https://1drv.ms/u/s!AnbNXrfr-X8jesAw-HHvHh1Z-58

I would like to have a combined data (Customer & Item) for "Sum Benefit from Selling Price".  When editing this sample file, there is something wrong with the "Item" table, the quantiy & Turnover don't match correctly, I'm lost. But, the formula "Sum Benefit from Selling Price" is fine.

Many thanks.

Rachel

Community Champion

Hey @EZV12 ,

I'm not sure if I fully understand the question.

SUMX is iterating through a table line by line. It can be a virtual or a real table. Then of course you can access all columns in the current table. So you could access Items and Sales:

``mySum = SUMX( myTable, myTable[Item] * myTable[Sales] )``

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Helper III

Dear Denis,

Thanks for your reply, however, I may not explain clearly my problem.

The "Item" and "Customer" are from different tables, and "Sales" or other data/figures are from other table as well.  I would like to combine both criterias "Customer" & "Item" into 1 without duplicates and with the corresponding "Sales" data or other data for each "Customer & Item".

So how can I figure this out?

Thanks.

Rachel

Community Champion

Hey @EZV12 ,

that's possible, but it depends very much on your data.

Can you share the tables, how they are connected and how you want to get the right value for your customer? Then I can send you the solution.

Best regards

Denis

Helper III

Hello Denis,

Thank you very much for your reply.  Here is the sample pbix file.

https://1drv.ms/u/s!AnbNXrfr-X8jesAw-HHvHh1Z-58

I would like to have a combined data (Customer & Item) for "Sum Benefit from Selling Price".  When editing this sample file, there is something wrong with the "Item" table, the quantiy & Turnover don't match correctly, I'm lost. But, the formula "Sum Benefit from Selling Price" is fine.

Many thanks.

Rachel

Community Champion

Hey Rachel ( @EZV12  ),

I'm wondering how the result should look like?

From the data model you have everything you need. You can just use the Customer and Items from the dimensional table and then the Sales from the fact table:

But I think I didn't fully understand what exactly you want to do. Can you show how the result should look like?

Best regards
Denis

Super User IV

@EZV12 , Why you need values or groups by here

Unless need to do a calculation we do not need group

Try like

SUMX(Table, [Sales])

Proud to be a Super User!

Helper III

Thank you for your reply, in fact, the "Item" and "Customer" are from different tables, and "Sales" or other data are from other table as well.  So how can I figure this out?

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.