cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EZV12
Helper III
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?

 

Thanks for your help.

Best regards

14 REPLIES 14
v-deddai1-msft
Community Support
Community Support

Hi @EZV12 ,

 

Would you please why you use SAMEPERIODLASTYEAR in the formula?

Capture61.PNG

 

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

 

 

@v-deddai1-msft 

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.

Hi @EZV12 ,

 

Please follow the steps below:

 

Create a new table :

 

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

 

Capture62.PNG

 

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])))

 

test5.gif

 

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

@v-deddai1-msft 

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!

 

Hi @EZV12 ,

 

Sorry , I don't understand your requiremts, please show me your expected output with some explanation.

 

Best Regards,

Dedmon Dai

v-deddai1-msft
Community Support
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

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.  

May you please help me to figure it out? 

Many thanks.

Rachel

selimovd
Community Champion
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
 

@selimovd 

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

 

selimovd
Community Champion
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

@selimovd 

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.  

May you please help me to figure it out? 

Many thanks.

Rachel

selimovd
Community Champion
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:

selimovd_0-1625754007236.png

 

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
 

 

amitchandak
Super User IV
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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak 

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?

Thanks in advance for your help.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors