Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LasseA
Helper I
Helper I

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
v-yulgu-msft
Employee
Employee

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.

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

Hi @LasseA,

 

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

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. 

 

Sorry, I cannot understand based on above description. Please post your sample data and the desired output.

How to Get Your Question Answered Quickly

 

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.

OrderSalesPrice = CALCULATE(SUM(Data[SellPriceExclVAT]);ALLEXCEPT(Data;Data[OrderNo]))

 

I have made this DAX code to sum my salesprices for each order number. 

 

In the column "SellPriceExclVat" i have a sellprice i do NOT want in my sum of salesprice pr order. 

 

How do i filter this sellprice out?

 

The sellprice i do not want summed is connected to another column "ProductNumber" and is called "Package".

 

Can i somehow filter this sellprice out? 

 

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 

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

 

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?

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

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. 

 

 

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

Only this simple change

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.