Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Hi @LasseA,
Suppose your table structure looks like:
You can directly drag [Order Number] and [SalesPrice] into a table visual, choose "Sum" as aggregation function.
Alternatively, you can create a calculated column in data table.
sum salesprice = CALCULATE(SUM(Table1[Salesprice]),ALLEXCEPT(Table1,Table1[Order Number]))
Best regards,
Yuliana Gu
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
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?
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |