Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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]))
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
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
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
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
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] )
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
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
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
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?
@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])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
50 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |