cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LasseA Regular Visitor
Regular Visitor

Salesprices within the same table. Combine to 1 order number within the same column.

Hello Power BI experts!

 

I have a question i hope you can help me solve. 

 

I have a large dataset with hundres of columns and different informations about sales. 

 

The salesprice is one of the columns, but within this column are salesprice for multiple products, related to the same order number.

 

When i used to clean this data i used SUMIFS and related it to the order number. I then had all the salesprices combined for each order number. 

 

How do i perform the same procedure in Power BI? 

 

Thanks in advance!

11 REPLIES 11
rafaelmpsantos Established Member
Established Member

Re: Salesprices within the same table. Combine to 1 order number within the same column.

Sumif in DAX is something like

MEASURE = 
calculate( sum(Table[Data]);
Filter(
all(table[Data]);
Table[Data]="SOMETHINGTHATYOULIKE"
)
)

 If it solve your problem, please mark as solved and give your positive feedback.

Rafael Mendonça
MCSA Bi Reporting
www.youtube.com/rafaelmendonca

 

LasseA Regular Visitor
Regular Visitor

Re: Salesprices within the same table. Combine to 1 order number within the same column.

So you would not create a new column but do it as a measure instead?

 

Would my measure be updated automatically when i opdae my table each morning?

rafaelmpsantos Established Member
Established Member

Re: Salesprices within the same table. Combine to 1 order number within the same column.

A measure is not a fixed value, they will always be updated every time that you use and may change based on aplied filters.

If it solve your problem, please mark as solved and give your positive feedback.

Rafael Mendonça
MCSA Bi Reporting
www.youtube.com/rafaelmendonca

LasseA Regular Visitor
Regular Visitor

Re: Salesprices within the same table. Combine to 1 order number within the same column.

I understand. 

 

I need to write a code that combine the different salesprices of different products and connect them to 1 order number. 

 

This is a fixed value and does not change. 

 

The only thing that changes in the table when i update is that more lines are added and more order numbers and salesprices are added. The same procedure must then happen to the new lines. 

 

 

Highlighted
rafaelmpsantos Established Member
Established Member

Re: Salesprices within the same table. Combine to 1 order number within the same column.

MEASURE = 
calculate(
          sum(Table[Data]);
          Filter(
                 all(table);
                 Table[Data]="SOMETHINGTHATYOULIKE"
                 )
          )

Only this simple change

LasseA Regular Visitor
Regular Visitor

Re: Salesprices within the same table. Combine to 1 order number within the same column.

I will try. Thank you. 

 

I dont understand the "Somethingthatyoulike" part. Is that the name or value of what my measure is going to do?

Community Support Team
Community Support Team

Re: Salesprices within the same table. Combine to 1 order number within the same column.

Hi @LasseA,

 

Suppose your table structure looks like:

1.PNG

 

You can directly drag [Order Number] and [SalesPrice] into a table visual, choose "Sum" as aggregation function.

2.PNG

 

Alternatively, you can create a calculated column in data table.

sum salesprice = CALCULATE(SUM(Table1[Salesprice]),ALLEXCEPT(Table1,Table1[Order Number]))

3.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LasseA Regular Visitor
Regular Visitor

Re: Salesprices within the same table. Combine to 1 order number within the same column.

Hey Yuliana! 

 

Thank you so much for your help. 

 

I have one more question. 

 

In my data i also have a salesprice which are the other salesprices combined, but without a "fee". 

 

This salesprice makes my overall salesprice "double" and my data useless. 

 

How do i get this salesprice "out"? 

 

In your logic it would be product B, and this salesprice i do not want to have in my visuals. 

 

Thanks in advance!

 

Best regards

Lasse 

LasseA Regular Visitor
Regular Visitor

Re: Salesprices within the same table. Combine to 1 order number within the same column.

Furthermore, when i create the calculated column, i get a sum of salesprice for each ordernumber line. This is wrong because each order number only have 1 sum of salesprice. 

 

How can i solve this problem?

 

Thank you so much in advance. 

 

Best regards, 

Lasse