Great job explaining this. I am an absolute whiz with excel and just after watching your video, never using Power BI, I feel like I could open it up and start creating some pretty useful tables and graphs. Really appreciate you taking the time to create this video.
Alex, you probably already knew this and did it just for demonstration purposes, but for anybody watching this, SUMX is redundant in a calculated column the way it was used here because in a calculated column, it's already evaluating row by row. The below would've worked just the same in this example at 10:05: ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * [Units Sold] Second note (because this took me way longer to stumble across than I would've liked!): Instead of nested IF statements, look into the SWITCH function. It essentially lets you put all the conditions you would've put in a nested IF into one function. :D EDIT: I meant to mention that Alex will be one of only four people ready for the apocalypse when it hits. I see you on the list of customers, Alex. 😂
Hi Wilson, thanks for pointing out the redundancy of SUMX. Could I please add that the formula you mentioned could have a little correction for [Units Sold] which comes from 'Apocolypse Sales' table. Below is a revised version that works: Profit_Column = ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * sum('Apocolypse Sales'[Units Sold])
@@christinawu3116 Hey Cristina, you're totally correct. I meant to use the [Sum of Products Sold] measure Alex created in the Apocalypse Sales table, which is also effectively what you're referencing in your correction here. Thanks for pointing that out. :D
@@christinawu3116 That revision doesn’t work, either. Both the sumx and your revision give the incorrect answer. What you get if you do that is the profit of a product * the sum of all products in the sales table. It should be the profit of a product times the sum of only that products sales. I made a separate column in apocalypse store column and made it equal to the measure [Sum of Products Sold] that was done earlier in the video. That gives the correct amount of product sold for each product and named it Products_Sold. Then I used that column to multiply it to the profit. So ([Price] - [Production Cost]) * [Products_Sold]. That will give you the correct profit.
Hello Alex Thank you so much for sharing this. I think you made an error at 09:04 . I think first you were supposed to calculate profit for each Product which will be Profit_for_each Product = 'Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost] then you calculate total_profit for each product which will be Total_Profit = 'Apocolypse Sales'[Sum of Products Sold] * 'Apocolypse Store'[Profit_for_each Product]
I believe so as well. Also, when performing the SUMX, it's better to just add it as a measure instead of a column in the table. That way we can get the total profit for each product and the aggregated profit for all products as well.
Alex, I get it you just used some random examples to explain sum and sumx and you explained them pretty well. But the profit result value is wrong, you can't calculate profit by (sum(all selling prices)-sum(all cost prices) * sum(all units)) because each product has different selling price and cost price. correct method is: for each product = SP-CP * units sold(of that particular product) then sum all these values correct profit value = 43212.43 Love your content. helped me a lot
bro he done exact same thing, and what u did is what he wanna conbvesy the difference between sum and sumx, to get the individual sum from sumx like u said in ur ans.
If people learn the DAX theories properly such as row context, filter context, context transition and iterators then you're on the path of becoming a pro at DAX.
Thank Alex for this. It doesn't make sense yet because I'm just getting started on my Data Analysis Journey but I'm hoping to come back here in a few weeks and brag about how this video is useful for me. P.S. I've saved this in my UA-cam Library for future references.
Hello @Alex. I'm pretty sure that the answer is this for the SUMX around 10:00: SUMX = CALCULATE( SUMX( 'Apocolypse Sales', 'Apocolypse Sales'[Units Sold] * (RELATED('Apocolypse Store'[Price]) - RELATED('Apocolypse Store'[Production Cost])) ) )
Hey Alex , You are an amazing teacher for real. Thanks a lot for explaining these concepts so well . Its really helping all of aspiring Data and Business Analyst a lot. I really feel your videos are very engaging and so informative .
Hey Alex. I hope you get to read this. The profit that was calculated with SUMX is incorrect. You multiplied the profit of each product with the sum of all products sold instead of the sum of each individual product. I made a separate column in apocalypse store table and made it equal to the measure [Sum of Products Sold] that was done earlier in the video. That gives the correct amount of product sold for each product and named it Products_Sold. Then I used that column to multiply it to the profit. So in a new column I put ([Price] - [Production Cost]) * [Products_Sold]. That will give you the correct profit.
@@TheDavesterist hey, i was able to do it by adding 'RELATEDTABLE' before the column name. This is the formula i used Profit Column SUMX = SUMX(RELATEDTABLE('Apocolypse Sales'), 'Apocolypse Sales'[Units Sold] * ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]))
At 9:04 It is mentioned that Apocalypse Sales is the table we are looking here, but the table we were working on was Apocalypse Store. Can you explain that part please? Also, when I tried to change the Sales to Store in the SUMX, it gave error. I am new to PowerBI, so maybe asking a bad question, but it would be helpful if I could get an explaination. Thanks.
I don't know if you have gotten an answer to this (maybe on your own), but i think its because in our formular, we have "unit price" and unit sold is in the sales table and not the store table
Hey Alex , good video .You are good teacher for real. Hopefully u get the time to do more videos on power bi and dax. Took your advice annd I completed the GDA courses at coursera and got my certificate. Thank you for that. "Great courses". I felt power bi should be included as well.
Hey Alex, great job! I just wanted to give you a quick heads up about a small correction that needs to be made to the calculation for the Profit column. It seems like the formula used in the video isn't giving the right results. Here's a suggestion for a different formula that should work better: Profit = ('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost])*SUMX(VALUES('Apocolypse Sales'[Product ID]),CALCULATE(SUM('Apocolypse Sales'[Units Sold])))
The Above formula works perfectly. Profit_column_Sumx= ('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost])*SUMX(VALUES('Apocolypse Sales'[Product ID]),CALCULATE(SUM('Apocolypse Sales'[Units Sold]))) Example: The calculation for product Id 10001 is Profit_column_sumX= (30.99-13.67)*390 is 6754.8. The answer in this vedio is not correct. but if we use the above formula we are getting the proper result. Thanks Leyla Alkan.
In 9:06 , I think why we are using 'Apocalypse Sales' as the table is because Price and Production Cost are already aggregated and populated in all rows but Units Sold is just a row. So we would need to aggregate or iterate it, which is why we are using the table that contains Units Sold. At least, that's how i understand it 🤷🏾♂️
Wow! This video was packed full of little gems. I really enjoyed learning the difference between SUM and SUMx as well as the IF statement at the end of the video. As always, THANK YOU ALEX!
It is a great video. When I was going through it I was little bit confused by the profit_column calculation as it was not matching with my pre-made profit_measure. so for little modification ---> Profit by Product = CALCULATE( (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost])) * SUM('Apocolypse Sales'[Units Sold]), FILTER( ALL('Apocolypse Sales'), 'Apocolypse Sales'[Product ID] = EARLIER('Apocolypse Store'[Product ID]) ) ) -->here are 2 parts inside calculate(). One is the equation and another is the filter(). The filter() gets only those product_ids from sales table that are present in the store table. After getting those product_ids from sales table, it simply applies the formula on it. I hope it helps. Also luckily gives me same output as my profit_measure.
Hi Alex, thank you very much for this clear and well articulated video. Makes the learning experience a bit easier. To more tutorials and development :)
I'll try me best to explain it, I myself don't fully have the grasp of it yet. The function SUMX iterates through the first table. In the instance where it is used in this tutorial, the column 'Units Sold' is located in the first table (Apocalypse Sales). This function (SUMX) will then use it's relationship to fetch the date from 'Apocalypse Store'. If the first table you provide after SUMX is 'Apocalypse Store' it will iterate through it but will not find the 'Units Sold' column, therefore it will throw an error. This is a basic explanation. Feel free to search for more details as the is my limit of understanding for now.
Hey Alex, Thanks for the videos, I've been learning a lot from you. Quick doubt, shoudn't we get the profit from the difference of price and product costs multiplied by the sum of the product sold, and not by the TOTAL (3001) of product sold?. Doing by the TOTAL of product sold, we are not getting the profit by Product but the Total Revenue. Am I Thinking wrongly? Appreciate your videos!!
Hi Alex, on the video (9:04), you said we put the Apocolypse Sales because this is the table we are looking at here but actually this is not. this table is Apocolypse Store. Would you explain why you put Apocolypse Sales instead of Apocolypse Store?? Profit_column_SUMX = sumx ('Apocolypse Sales', ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold])
Easier solution: First make a profit column: Profit_column = 'Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost] Second make a Total profit column: Total_profit = 'Apocolypse Store'[Profit_column] * SUM('Apocolypse Sales'[Units Sold])
Thank goodness for chartGPT, I almost gave up for those freshers like me that struggled use this SUMX( 'Apocolypse Sales',('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold])
hey i calculated the value manually for the profit just to cross verify but am getting different values than the one when i use the formula from the video, i am using the exact dataset provided by you and am following the video step by step thoroughly. please do reply if possible
Hi Alex thank you so much for your videos ..but unfortinately i couldn't understand the part of SUMX i tried to calculate by myself but i found different results for every customer
09:04 May I know why did we use 'Apocalypse Sales' instead of 'Apocalypse Store'? The table we are working on is 'Apocalypse Store'. When I tried 'Apocalypse Store', it gave an error. As far I understand, Units Sold is in the 'Apocalypse Sales' Table, and maybe that's why it gave an error when I used 'Apocalypse Store', instead of 'Apocalypse Sales'. Am I right?
Thanks so much Alex, but I think it have a little mistake from your tutorial. About sum and sumx, you use (price - production cost) * amount but each products have another profits so when you multi it have problem.
hello Alex, nice video series. I just wanted to ask a doubt - when we are calculating profit we use following DAX func - PROFIT = (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost]))* SUM('Apocolypse Sales'[Units Sold]) Here the SUM of all the prices is done first? why are we using SUM at all when we are subtracting single values in Apocalypse_Store table?
Your column at 07:45 makes no sense to me. Take for example the nylon rope. You are doing (30.99 - 13.67) * 3001. But 3001 is the total amount of products that has been sold, not the amount of nylon rope that has been sold. So what are you even calculating? That number has no meaning.
If you were stuck like me in the Profit colmun => Profit Column = ('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost])*SUM('Apocolypse Sales'[Units Sold])
Alex, are you sure you calculated the SUMx in a correct way? Because it multiplies each row by 3001 so the total number of products sold, not individual for each row. For example, duct tape - 2.75 - 1.01 = 1.74 this is a profit for one duct tape, and then you multipled it by 3001 not 365 so 1.74 * 3001 = 5221.74. Shouldn't it be 1.74 * 365 = 635.1? Please reply because i've spent a lot of time being confused and trying to understand how it works ;)
I suppose that in this measure we should use RELATED function - without it we obtain uncorrect result if someone can confirm. Profit = VAR Total_Sales = SUMX ( 'Apocolypse Sales', 'Apocolypse Sales'[Units Sold] * RELATED ( 'Apocolypse Store'[Price] ) ) VAR Total_Cost = SUMX ( 'Apocolypse Sales', 'Apocolypse Sales'[Units Sold] * RELATED ( 'Apocolypse Store'[Production Cost] ) ) VAR Profit = Total_Sales - Total_Cost RETURN Profit
It looks like it took the sum of all the units sold instead of just the units sold by product id. He made a table of units sold earlier in the video and the sum was 3001.
Thank you! I thought I had gone crazy! I looked at the digits and couldn't figure out how on earth the total profits calculated with SUMx got so big! Now we need another video on SUMx, Alex))
Thank you very much alex. Please in the second example I have the same results by clicking unit sold without creating a new measure its looks like sum of unit sold already exist in the Apocolypse sales table
From the comments I understand some formulas might be wrong , but going by simple maths , in the sales table create total revenue , total cost measures and use them for profit measure. TotalRevenue = SUMX('Apocolypse Sales', 'Apocolypse Sales'[Units Sold] * RELATED('Apocolypse Store'[Price])) TotalCost = SUMX('Apocolypse Sales','Apocolypse Sales'[Units Sold] * RELATED('Apocolypse Store'[Production Cost])) Profit = 'Apocolypse Sales'[TotalRevenue]-'Apocolypse Sales'[TotalCost] For the profit per product / profit column Profit_per_product = SUMX(RELATEDTABLE('Apocolypse Sales'),'Apocolypse Sales'[Units Sold]* ('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost])) I stand to be corrected and I believe Alex was right ,he just emphasized on the difference between SUM and SUMX inplace of correct output . Cheers
They are both tools to deliver the same kind of data products. Here is a nice video of Luke Barousse talking about the differences: ua-cam.com/video/axgNfM55dw8/v-deo.html
Hi Alex, Nice to meet you, Indeed appreciable effort. I had a concern about the DAX chapter profit calculation . But when i was going to address it , i found the same from the other people. By the way, just curious to know why dont you edit the video or make a short video to revise it ? Thanks
awesome work but @7:06 these number are not right, Total profit is 43K not 434K, I checked in excel and that because it multiply the profit for each item with the total sold item 3001 not total sold item per each item, to be honest I dont know how to fix that, does anyone know ?
Hey Alex, For the Sum function columns we took from both sales and store tables. When we are going with the SUMx function you took the table name "Apocalypse Sales", then got the answer as correct. But when I take "Apocalypse Store" as a table in SUMx then the function does not show the 'Apocalypse [units sold]' column. Any reason for selecting a particular table when using these functions Because When we chose the "Apocalypse Sales" table only the 'Apocalypse [units sold]' Column is activated or highlighted in the Function. so, Please clarify me if there is any particular reason behind it.
I had the same problem but then realised that I should use "Apocolypse Sales" table in the SUMX function, not "Apocolypse Store". This way, I have access to the whole table and can use its columns in the store table.
I am followining your tutorial with Power BI open, and performing all the calculations. Sometimes PBI miscalculates some tables, but then suddenly returns to the correct results, I dont know the causes, but I think is becuse the movement of fields when doing the work
Hi, I am confused about the example you gave for SUMX. From my understanding, the function subtracts the production cost from the price of each product and multiples it by the total number of units sold (in this case 3001). Shouldn't we multiply by the number of products that sold for each unit. For example Nylon Rope's were sold 390 times and the profit for each nylon rope is $17.32. So wouldn't the total profit for Nylon Ropes be $17.32 x 390 which is $6754.80. The function is mutliplying $17.32 by 3001 which is $51977.32.
the sumx function, first part you said "Apocolypse sales" but that table is actually "Apocalypse store", I do not understand... could you explain Alex?
So there's 2 things wrong with his calculation 1 is the formula structure, the DAX measure is like a calculator ots not recognising that the order of the calculation is not multiplication first, easy way to resolve this is to bracket off the values that are minusing thwn multiply, second issue is that Alex is multiplying by the sum of units for individual rows whixh isn't correct, I am still trying to find a way to solve this😂😂 tearing my hair out but I'm looking into calculation function
Hey everyone. Hope you all are having a good time learning power BI. I just started the video series and had a doubt regarding the SumX feature. At 9:06 he says, he is applying sumx in the table 'apocalypse sales' so he writes it in the formula, but actually he is applying it in the table 'apocalypse store'. Can anyone explain why he wrote sales instead of store at the beginning of the SumX formula? Or is it just to call in the table into the formula?
Profit_Column_SUMx = SUMX('Apocolypse Sales', ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold]) 1.SUMX('Apocolypse Sales', ...): This part of the formula is using the SUMX function. The SUMX function iterates over each row in the 'Apocolypse Sales' table and performs a specified calculation for each row. 2.('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold]: Inside the SUMX function, this part calculates the profit for each row in the 'Apocolypse Sales' table. It subtracts the production cost from the price for each product (from the 'Apocolypse Store' table) and then multiplies the result by the number of units sold (from the 'Apocolypse Sales' table). 3.Overall Functionality: The SUMX function then adds up the calculated profit for each row, giving you the total profit across all rows in the 'Apocolypse Sales' table.
Thanks Alex, simple and clear explanation, but what is the difference between a conditional column and IF statement? can we use it instead of using IF?
Hi Alex, quick question, why is the profit distribution along the products different when i use the visualization table instead. note that my viz table consist of product ID, units sold, revenue (a new measure, price * units sold) and then profit( which is also a new measure).
Hey Alex, What to do if left function isn’t working on a column and you need to get 7-8 characters from the left of that column in another column. Please Advise
Hello Alex i tried :TotalProfix = SUMX('Apocolypse Store',('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * ('Apocolypse Sales'[Units Sold])) but still cannot find apocolpyse sales table and giving me this MSG : A single value for column 'Units Sold' in table 'Apocolypse Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum
use apocolypse sales instead of store as your chosen table , idk why we need to that cause we are working on apocolypse stores here but it just needs to be done
Hello Alex, Hope you are doing amazing. I am doing your bootcamp and following all the steps. But I got stuck in this video because of PowerBI app. When I wanted to connect the workbook in the powerBI, it shows me this error message 'THERE ARE PENDING CHANGES IN YOUR QUERIES THAT HAVEN’T BEEN APPLIED ' I waited hours and hours but it doesn't load the data. What can I do? Please help me with this?
Hey Alex, I've just completed my Google DA certificate. I also have a prior 1 yr experience as a Quality Analyst. Can you review my Resume or give any suggestions for seeking a job.
Hello I have question for you when i create visualization table with (product name and profit column ) and (product name and profit column sumx) gave me different answer why this happen?
hey.. This is the formula i used Profit Column SUMX = SUMX(RELATEDTABLE('Apocolypse Sales'), 'Apocolypse Sales'[Units Sold] * ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]))
Just wonder, have ever reconsile that data ? Because if i got manualy calculate all the transaction value which is (unit sold * price) is all total only 87,945.09. how cold your Dax calculate end up with profit 434,754.87 CMIIW
you're right. profit result value is wrong, can't calculate profit by (sum(all selling prices)-sum(all cost prices) * sum(all units)) because each product has different selling price and cost price. correct method is: for each product = SP-CP * units sold(of that particular product) then sum all these values correct SP = 87945.09 correct CP = 44732.66 correct profit value (SP-CP)= 43212.43
Hi Alex, thank you so much for your tutorial. I actually made it to the last step but the IF statement is giving me an #error on my order_size column and ‘cannot find table ‘big order’ I entered this: IF(‘table name’[Units Sold]>25, ‘Big Order’, ‘Small Order’) Did I miss anything? Like spacing etc? Thank you
is it a bad practice that I did this Profit = (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost])) * [Total product sold] instead of this Profit = (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost])) * SUM('Apocolypse Sales'[Units Sold]?
Hey Alex, I was doing the math and the value of the profit per item is WRONG. It is multiplying [price - cost] of the product 10001 by the total of units sold (3001), therefore the value of 59k of profit. The corret value is multiplied by 390 (Total amount sold of the item 10001) which result a total of 6754,8. I tried to fix it in my Power BI but, since I'm learning, I couldn't find a solution. If you ever see this comment or anyone who knows how to fix it, please teach me!
Hey there, god bless your efforts in this channel. I have only a simple enquiry as an new sql learner. != Are these two comparison operators mean the same thing in sql syntax? Thanks for taking care of this.
Hey, if you take literally the question you asked here and copy paste it to google, you can get an answer instantly instead of waiting for another person to respond. :D
Hi Alex, after the getting the Profit.. I placed the customer over the profit but it didn’t split the profit between the customers.. it was showing the total figure in front of all the customers.. What to do anybody😢😢
Can someone please explain why in the SUMX experession: " Profit_Column_sumX = SUMX( 'Apocolypse Sales', ( ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold] ) )" We used "Apocolypse Sales" in this part ( SUMX(, ))and not "Apocolypse Store", even though we are adding the column to "Apocolypse Store"??
Here is the profit formula i used but it gave me negative answers like '-200' I don't know if my formula is wrong or ?? Profit = SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost]) * SUM('Apocolypse Sales'[Units Sold]). Please let me know the error if you find any. Thanks
for anyone having the same problem in the order of operations, multiplication, and division come before addition and subtraction so you have to separate the subtraction from the multiplication with parenthesis Profit = (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost])) * SUM('Apocolypse Sales'[Units Sold])
I can't view the tables in Table View. In the model view everything is visible but not in the Table view. Can someone help, please? Do you know how I can fix that?
Hey, why I can't make the Units Sold By Year and it only does :"Sum of Units Sold By Year", I tried to don't summarize but it didn't help. now it counts it. Please help.
hello I Used The Sumx Just LIke The tutorial You'v been learn But The Power Bi Throws An Error About You Cant Use The UnitSold Column Of The Second Dataset May Any One Help Me ??? The Full Error Is : ' single value for column in table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregator such as min, max, count, or sum to get a single result. '
I do not understand why you have used (SUM(Price) - SUM(Productioncost))*SUM(Unitssold). Shouldn't it be SUM((Price - Productioncost)*Unitssold). Cause profit will be different for each product. I believe what you did what calculated entire profit for one unit and multiply it with the total units of all products bought by the customer.
@@jf_romo8113 You wrote this a long time ago, but I will respond in case someone else has the same question. I made some tests in Excel, and it seems like this UA-camr has done the math incorrectly. The only way to get the numbers in his table, is if you first take the sum of ALL prices and subtract that by ALL product costs, then multiply by the number of products that the customer bought. This makes zero sense, of course, because the customer did not buy all products, and certainly did not buy the same amount of each product.
Great job explaining this. I am an absolute whiz with excel and just after watching your video, never using Power BI, I feel like I could open it up and start creating some pretty useful tables and graphs. Really appreciate you taking the time to create this video.
Happy to do it! I love making these tutorial videos :)
This was Microsoft's plan with PowerBI running on Power Query and M language.
Thanks so much Alex for taking your time to break this down to the smallest bits for us.
I have gained a lot from this video.
Alex, you probably already knew this and did it just for demonstration purposes, but for anybody watching this, SUMX is redundant in a calculated column the way it was used here because in a calculated column, it's already evaluating row by row. The below would've worked just the same in this example at 10:05:
('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * [Units Sold]
Second note (because this took me way longer to stumble across than I would've liked!): Instead of nested IF statements, look into the SWITCH function. It essentially lets you put all the conditions you would've put in a nested IF into one function. :D
EDIT: I meant to mention that Alex will be one of only four people ready for the apocalypse when it hits. I see you on the list of customers, Alex. 😂
Lolol good tip and absolutely :D
Hi Wilson, thanks for pointing out the redundancy of SUMX. Could I please add that the formula you mentioned could have a little correction for [Units Sold] which comes from 'Apocolypse Sales' table. Below is a revised version that works:
Profit_Column = ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * sum('Apocolypse Sales'[Units Sold])
@@christinawu3116 Hey Cristina, you're totally correct. I meant to use the [Sum of Products Sold] measure Alex created in the Apocalypse Sales table, which is also effectively what you're referencing in your correction here. Thanks for pointing that out. :D
@@christinawu3116 That revision doesn’t work, either. Both the sumx and your revision give the incorrect answer. What you get if you do that is the profit of a product * the sum of all products in the sales table. It should be the profit of a product times the sum of only that products sales.
I made a separate column in apocalypse store column and made it equal to the measure [Sum of Products Sold] that was done earlier in the video. That gives the correct amount of product sold for each product and named it Products_Sold. Then I used that column to multiply it to the profit. So ([Price] - [Production Cost]) * [Products_Sold]. That will give you the correct profit.
@@christinawu3116 Thankyou so much
Hello Alex Thank you so much for sharing this. I think you made an error at 09:04 . I think first you were supposed to calculate profit for each Product which will be Profit_for_each Product = 'Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost] then you calculate total_profit for each product which will be Total_Profit = 'Apocolypse Sales'[Sum of Products Sold] * 'Apocolypse Store'[Profit_for_each Product]
I believe so as well. Also, when performing the SUMX, it's better to just add it as a measure instead of a column in the table. That way we can get the total profit for each product and the aggregated profit for all products as well.
Thank you. That means the Profit_column as well is incorrect 434754.87 if I will total all profit each product.
The correct total is 43,212.43
There was a mistake there
i was wondering the same, thanks
Alex, I get it you just used some random examples to explain sum and sumx and you explained them pretty well.
But the profit result value is wrong, you can't calculate profit by (sum(all selling prices)-sum(all cost prices) * sum(all units)) because each product has different selling price and cost price.
correct method is: for each product = SP-CP * units sold(of that particular product)
then sum all these values
correct profit value = 43212.43
Love your content. helped me a lot
bro he done exact same thing, and what u did is what he wanna conbvesy the difference between sum and sumx, to get the individual sum from sumx like u said in ur ans.
That approach won't work with the data he is using. For it to work, both Price and Production Cost must be in the Sales table.
If people learn the DAX theories properly such as row context, filter context, context transition and iterators then you're on the path of becoming a pro at DAX.
Thanks bro
@nayeem3905 Any youtube channel for these DAX basics?
Thank Alex for this. It doesn't make sense yet because I'm just getting started on my Data Analysis Journey but I'm hoping to come back here in a few weeks and brag about how this video is useful for me.
P.S. I've saved this in my UA-cam Library for future references.
Hello @Alex. I'm pretty sure that the answer is this for the SUMX around 10:00:
SUMX = CALCULATE(
SUMX(
'Apocolypse Sales',
'Apocolypse Sales'[Units Sold] *
(RELATED('Apocolypse Store'[Price]) - RELATED('Apocolypse Store'[Production Cost]))
)
)
This is the correct DAX formula. Could you please explain how you got to this formula?
That's Right , could you please explain why did you use calculate at first and without any filters ?
that's would help me a lot
Pretty sure that it works without the "Calculate" btw. Sumx, does the sum line by line and related go search the info in an other table
@@antoinerousset1948 I tried it without calculate , it didn't work that's why i asked
any way , thanks for your help
Hey Alex ,
You are an amazing teacher for real. Thanks a lot for explaining these concepts so well . Its really helping all of aspiring Data and Business Analyst a lot. I really feel your videos are very engaging and so informative .
Hey Alex. I hope you get to read this. The profit that was calculated with SUMX is incorrect. You multiplied the profit of each product with the sum of all products sold instead of the sum of each individual product.
I made a separate column in apocalypse store table and made it equal to the measure [Sum of Products Sold] that was done earlier in the video. That gives the correct amount of product sold for each product and named it Products_Sold. Then I used that column to multiply it to the profit. So in a new column I put ([Price] - [Production Cost]) * [Products_Sold]. That will give you the correct profit.
If anybody knows a cleaner way of doing it, let me know. I couldn’t figure out how to do it without making that extra Products_Sold column I made.
@@TheDavesterist hey, i was able to do it by adding 'RELATEDTABLE' before the column name.
This is the formula i used
Profit Column SUMX = SUMX(RELATEDTABLE('Apocolypse Sales'), 'Apocolypse Sales'[Units Sold] * ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]))
@@mahimaparekh6254 That was helpful. Thanks!
@@mahimaparekh6254 thank you very much from Kazakhstan!)
Thank you so much for your answer@@mahimaparekh6254!!
At 9:04 It is mentioned that Apocalypse Sales is the table we are looking here, but the table we were working on was Apocalypse Store. Can you explain that part please? Also, when I tried to change the Sales to Store in the SUMX, it gave error. I am new to PowerBI, so maybe asking a bad question, but it would be helpful if I could get an explaination. Thanks.
Please where did he get the data from?
@@danieljohnson454 check description
I don't know if you have gotten an answer to this (maybe on your own), but i think its because in our formular, we have "unit price" and unit sold is in the sales table and not the store table
Hey Alex , good video .You are good teacher for real. Hopefully u get the time to do more videos on power bi and dax. Took your advice annd I completed the GDA courses at coursera and got my certificate. Thank you for that. "Great courses". I felt power bi should be included as well.
Alex thank you so much! Your explanations are super easy to follow, and Power BI seems very intuitive as you explain it
Love this new series. Keep up the great work Alex !
Hey Alex, great job! I just wanted to give you a quick heads up about a small correction that needs to be made to the calculation for the Profit column. It seems like the formula used in the video isn't giving the right results. Here's a suggestion for a different formula that should work better: Profit = ('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost])*SUMX(VALUES('Apocolypse Sales'[Product ID]),CALCULATE(SUM('Apocolypse Sales'[Units Sold])))
This works perfectly. But we already had profit measure so you are giving formula for Profit_Column_SumX just so people don't get confused.
Hi can you drop a measure for 'PROFIT' . The one in the video is wrong. Thanks
@@abosedetowoju5083 use this
Profit_Column = ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * sum('Apocolypse Sales'[Units Sold])
The Above formula works perfectly. Profit_column_Sumx= ('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost])*SUMX(VALUES('Apocolypse Sales'[Product ID]),CALCULATE(SUM('Apocolypse Sales'[Units Sold])))
Example: The calculation for product Id 10001 is Profit_column_sumX= (30.99-13.67)*390 is 6754.8. The answer in this vedio is not correct. but if we use the above formula we are getting the proper result.
Thanks Leyla Alkan.
@@abosedetowoju5083 Hi. Profit = SUMX('Apocolypse Store',('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost])*SUMX(VALUES('Apocolypse Sales'[Product ID]),CALCULATE(SUM('Apocolypse Sales'[Units Sold]))))
In 9:06 , I think why we are using 'Apocalypse Sales' as the table is because Price and Production Cost are already aggregated and populated in all rows but Units Sold is just a row. So we would need to aggregate or iterate it, which is why we are using the table that contains Units Sold.
At least, that's how i understand it 🤷🏾♂️
Please use sumx you might be using the sum.
I always wince at your battle cry of "WHATSGOINGONEVERYBODY!" :)
This is so well explained & it definitely helped me in preparing a report as an assignment! Thank you so much for sharing your knowledge & expertise!
I'm learning so much from your Power BI playlist! Thank you for these!
You're amazing! thank you for explaining a complicated thing (for me) in a simple way.
Alex thank you for this series, that was exactly what I need and I directly searched with your name to learn in a formal way!!
Wow! This video was packed full of little gems. I really enjoyed learning the difference between SUM and SUMx as well as the IF statement at the end of the video. As always, THANK YOU ALEX!
It is a great video. When I was going through it I was little bit confused by the profit_column calculation as it was not matching with my pre-made profit_measure.
so for little modification --->
Profit by Product =
CALCULATE(
(SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost])) * SUM('Apocolypse Sales'[Units Sold]),
FILTER(
ALL('Apocolypse Sales'),
'Apocolypse Sales'[Product ID] = EARLIER('Apocolypse Store'[Product ID])
)
)
-->here are 2 parts inside calculate(). One is the equation and another is the filter().
The filter() gets only those product_ids from sales table that are present in the store table. After getting those product_ids from sales table, it simply applies the formula on it.
I hope it helps.
Also luckily gives me same output as my profit_measure.
Great video! def feeling more comfortable with DAX expressions!
Short and to the point. Best! 👍
Thanks for this Sir Alex!
Really helpful series.
Hi Alex, thank you very much for this clear and well articulated video. Makes the learning experience a bit easier. To more tutorials and development :)
great explanation between sum and sumx function
Guys can anybody tell me at 8:59 why alex used apocalypse sales instead of store because the table were using is store instead of sales?
I'll try me best to explain it, I myself don't fully have the grasp of it yet.
The function SUMX iterates through the first table. In the instance where it is used in this tutorial, the column 'Units Sold' is located in the first table (Apocalypse Sales). This function (SUMX) will then use it's relationship to fetch the date from 'Apocalypse Store'. If the first table you provide after SUMX is 'Apocalypse Store' it will iterate through it but will not find the 'Units Sold' column, therefore it will throw an error.
This is a basic explanation. Feel free to search for more details as the is my limit of understanding for now.
Alex, thank you so much for breaking this down. please i am finding it difficult to copy and paste the Dax formular from the bar
Hey Alex, Thanks for the videos, I've been learning a lot from you.
Quick doubt, shoudn't we get the profit from the difference of price and product costs multiplied by the sum of the product sold, and not by the TOTAL (3001) of product sold?. Doing by the TOTAL of product sold, we are not getting the profit by Product but the Total Revenue. Am I Thinking wrongly?
Appreciate your videos!!
Hi Alex,
on the video (9:04), you said we put the Apocolypse Sales because this is the table we are looking at here but actually this is not. this table is Apocolypse Store. Would you explain why you put Apocolypse Sales instead of Apocolypse Store??
Profit_column_SUMX = sumx ('Apocolypse Sales', ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold])
ya, i spot this error too. Confusing but sad cause he don't reply
Thanks, I was confused a little bit.
Easier solution: First make a profit column: Profit_column = 'Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]
Second make a Total profit column: Total_profit = 'Apocolypse Store'[Profit_column] * SUM('Apocolypse Sales'[Units Sold])
Thank goodness for chartGPT, I almost gave up for those freshers like me that struggled use this SUMX( 'Apocolypse Sales',('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold])
Can please give me the correct formula for this ?
hey i calculated the value manually for the profit just to cross verify but am getting different values than the one when i use the formula from the video, i am using the exact dataset provided by you and am following the video step by step thoroughly. please do reply if possible
Great video
So helpful
Very helpful
Hi Alex thank you so much for your videos ..but unfortinately i couldn't understand the part of SUMX i tried to calculate by myself but i found different results for every customer
Yes I'm pretty sure that part is incorrect.
09:04
May I know why did we use 'Apocalypse Sales' instead of 'Apocalypse Store'? The table we are working on is 'Apocalypse Store'.
When I tried 'Apocalypse Store', it gave an error.
As far I understand, Units Sold is in the 'Apocalypse Sales' Table, and maybe that's why it gave an error when I used 'Apocalypse Store', instead of 'Apocalypse Sales'.
Am I right?
Thanks so much Alex, but I think it have a little mistake from your tutorial. About sum and sumx, you use (price - production cost) * amount but each products have another profits so when you multi it have problem.
hello Alex, nice video series. I just wanted to ask a doubt - when we are calculating profit we use following DAX func - PROFIT = (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost]))* SUM('Apocolypse Sales'[Units Sold])
Here the SUM of all the prices is done first? why are we using SUM at all when we are subtracting single values in Apocalypse_Store table?
Thanks so much, Alex
Your column at 07:45 makes no sense to me. Take for example the nylon rope. You are doing (30.99 - 13.67) * 3001. But 3001 is the total amount of products that has been sold, not the amount of nylon rope that has been sold. So what are you even calculating? That number has no meaning.
LOVE IT
If you were stuck like me in the Profit colmun => Profit Column = ('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost])*SUM('Apocolypse Sales'[Units Sold])
I am too , the unit sold column ain't coming up
good stuff 👍
Alex, are you sure you calculated the SUMx in a correct way? Because it multiplies each row by 3001 so the total number of products sold, not individual for each row. For example, duct tape - 2.75 - 1.01 = 1.74 this is a profit for one duct tape, and then you multipled it by 3001 not 365 so 1.74 * 3001 = 5221.74. Shouldn't it be 1.74 * 365 = 635.1? Please reply because i've spent a lot of time being confused and trying to understand how it works ;)
I suppose that in this measure we should use RELATED function - without it we obtain uncorrect result if someone can confirm.
Profit =
VAR Total_Sales =
SUMX (
'Apocolypse Sales',
'Apocolypse Sales'[Units Sold] * RELATED ( 'Apocolypse Store'[Price] )
)
VAR Total_Cost =
SUMX (
'Apocolypse Sales',
'Apocolypse Sales'[Units Sold] * RELATED ( 'Apocolypse Store'[Production Cost] )
)
VAR Profit = Total_Sales - Total_Cost
RETURN
Profit
It looks like it took the sum of all the units sold instead of just the units sold by product id. He made a table of units sold earlier in the video and the sum was 3001.
Thank you! I thought I had gone crazy! I looked at the digits and couldn't figure out how on earth the total profits calculated with SUMx got so big! Now we need another video on SUMx, Alex))
Alex is the GOAT 2024
Thank you very much alex. Please in the second example I have the same results by clicking unit sold without creating a new measure its looks like sum of unit sold already exist in the Apocolypse sales table
From the comments I understand some formulas might be wrong , but going by simple maths , in the sales table create total revenue , total cost measures and use them for profit measure.
TotalRevenue = SUMX('Apocolypse Sales', 'Apocolypse Sales'[Units Sold] * RELATED('Apocolypse Store'[Price]))
TotalCost = SUMX('Apocolypse Sales','Apocolypse Sales'[Units Sold] * RELATED('Apocolypse Store'[Production Cost]))
Profit = 'Apocolypse Sales'[TotalRevenue]-'Apocolypse Sales'[TotalCost]
For the profit per product / profit column
Profit_per_product = SUMX(RELATEDTABLE('Apocolypse Sales'),'Apocolypse Sales'[Units Sold]* ('Apocolypse Store'[Price]-'Apocolypse Store'[Production Cost]))
I stand to be corrected and I believe Alex was right ,he just emphasized on the difference between SUM and SUMX inplace of correct output . Cheers
Amazing Thanks Alex
9:00
Hey Alex, I think you made a mistake here... isn't the table we're referring to "Apocalypse Store" and not "Apocalypse Sales"?
May I know what is the difference compare to Tableau?
They are both tools to deliver the same kind of data products. Here is a nice video of Luke Barousse talking about the differences:
ua-cam.com/video/axgNfM55dw8/v-deo.html
Hi Alex, Nice to meet you, Indeed appreciable effort. I had a concern about the DAX chapter profit calculation . But when i was going to address it , i found the same from the other people. By the way, just curious to know why dont you edit the video or make a short video to revise it ? Thanks
awesome work but @7:06 these number are not right, Total profit is 43K not 434K, I checked in excel and that because it multiply the profit for each item with the total sold item 3001 not total sold item per each item, to be honest I dont know how to fix that, does anyone know ?
Hey Alex, For the Sum function columns we took from both sales and store tables. When we are going with the SUMx function you took the table name "Apocalypse Sales", then got the answer as correct. But when I take "Apocalypse Store" as a table in SUMx then the function does not show the 'Apocalypse [units sold]' column. Any reason for selecting a particular table when using these functions
Because When we chose the "Apocalypse Sales" table only the 'Apocalypse [units sold]' Column is activated or highlighted in the Function. so, Please clarify me if there is any particular reason behind it.
I have the same question
Hey, you have to select "Apocalypse Sales" itself, because "Apocalypse Store" doesn't have any tables names "Units Sold".
someone knows the answer to this question? I'm trying to understand it too
I had the same problem but then realised that I should use "Apocolypse Sales" table in the SUMX function, not "Apocolypse Store". This way, I have access to the whole table and can use its columns in the store table.
Learn about the use of RELATED
I am followining your tutorial with Power BI open, and performing all the calculations. Sometimes PBI miscalculates some tables, but then suddenly returns to the correct results, I dont know the causes, but I think is becuse the movement of fields when doing the work
Hi, I am confused about the example you gave for SUMX. From my understanding, the function subtracts the production cost from the price of each product and multiples it by the total number of units sold (in this case 3001). Shouldn't we multiply by the number of products that sold for each unit. For example Nylon Rope's were sold 390 times and the profit for each nylon rope is $17.32. So wouldn't the total profit for Nylon Ropes be $17.32 x 390 which is $6754.80. The function is mutliplying $17.32 by 3001 which is $51977.32.
same question
the sumx function, first part you said "Apocolypse sales" but that table is actually "Apocalypse store", I do not understand... could you explain Alex?
Hi Alex, thanks for the videos one question i have is when calculating the profit i get a negative value same for sumx could i be wrong
So there's 2 things wrong with his calculation 1 is the formula structure, the DAX measure is like a calculator ots not recognising that the order of the calculation is not multiplication first, easy way to resolve this is to bracket off the values that are minusing thwn multiply, second issue is that Alex is multiplying by the sum of units for individual rows whixh isn't correct, I am still trying to find a way to solve this😂😂 tearing my hair out but I'm looking into calculation function
Hey everyone. Hope you all are having a good time learning power BI. I just started the video series and had a doubt regarding the SumX feature. At 9:06 he says, he is applying sumx in the table 'apocalypse sales' so he writes it in the formula, but actually he is applying it in the table 'apocalypse store'. Can anyone explain why he wrote sales instead of store at the beginning of the SumX formula? Or is it just to call in the table into the formula?
Profit_Column_SUMx = SUMX('Apocolypse Sales', ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold])
1.SUMX('Apocolypse Sales', ...): This part of the formula is using the SUMX function. The SUMX function iterates over each row in the 'Apocolypse Sales' table and performs a specified calculation for each row.
2.('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold]: Inside the SUMX function, this part calculates the profit for each row in the 'Apocolypse Sales' table. It subtracts the production cost from the price for each product (from the 'Apocolypse Store' table) and then multiplies the result by the number of units sold (from the 'Apocolypse Sales' table).
3.Overall Functionality: The SUMX function then adds up the calculated profit for each row, giving you the total profit across all rows in the 'Apocolypse Sales' table.
Thanks Alex, simple and clear explanation, but what is the difference between a conditional column and IF statement? can we use it instead of using IF?
I think that Sumx in PowerBI is equivalent to the Partition By statement in SQL
Hello..on my y axis instead of units sold i was getting sum of units sold...how do i fix this?
Hi Alex, quick question, why is the profit distribution along the products different when i use the visualization table instead. note that my viz table consist of product ID, units sold, revenue (a new measure, price * units sold) and then profit( which is also a new measure).
why did we use apocolypse store in sum function and apocolypse salex in sumx, when we are adding column to the apocolypse store in both?
Hello Alex, Can I make any func I want using just AI?
Hey Alex,
What to do if left function isn’t working on a column and you need to get 7-8 characters from the left of that column in another column. Please Advise
hii alex
can you create a detailed power bi project video which explains all dax functions
Hello Alex
i tried :TotalProfix =
SUMX('Apocolypse Store',('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * ('Apocolypse Sales'[Units Sold]))
but still cannot find apocolpyse sales table and giving me this MSG :
A single value for column 'Units Sold' in table 'Apocolypse Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum
use apocolypse sales instead of store as your chosen table , idk why we need to that cause we are working on apocolypse stores here but it just needs to be done
@@aryanpndit but we didint understand why we using sales instead of sales
Hello Alex,
Hope you are doing amazing. I am doing your bootcamp and following all the steps. But I got stuck in this video because of PowerBI app.
When I wanted to connect the workbook in the powerBI, it shows me this error message
'THERE ARE PENDING CHANGES IN YOUR QUERIES THAT HAVEN’T BEEN APPLIED '
I waited hours and hours but it doesn't load the data.
What can I do?
Please help me with this?
Profit_Column_sumx = SUMX(RELATEDTABLE('Apocolypse Sales'), 'Apocolypse Sales'[Units Sold] * ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]))
Hey Alex, I've just completed my Google DA certificate. I also have a prior 1 yr experience as a Quality Analyst. Can you review my Resume or give any suggestions for seeking a job.
can u make more videos on dax
Hello I have question for you
when i create visualization table with (product name and profit column ) and (product name and profit column sumx) gave me different answer why this happen?
Anyone have a solution ti calculate the individual profits? Cause in this video Alex is multiplying every column by the sum 3001 qhoxh is incorrect
hey..
This is the formula i used
Profit Column SUMX = SUMX(RELATEDTABLE('Apocolypse Sales'), 'Apocolypse Sales'[Units Sold] * ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]))
Can someone please help me with the correct code for profit and sumx
When I run mine my values are incorrect and are in negative
Thank you
Alex
Profit_Column_SUMx = SUMX('Apocolypse Sales',('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold])
Just wonder, have ever reconsile that data ? Because if i got manualy calculate all the transaction value which is (unit sold * price) is all total only 87,945.09. how cold your Dax calculate end up with profit 434,754.87
CMIIW
you're right.
profit result value is wrong, can't calculate profit by (sum(all selling prices)-sum(all cost prices) * sum(all units)) because each product has different selling price and cost price.
correct method is: for each product = SP-CP * units sold(of that particular product)
then sum all these values
correct SP = 87945.09
correct CP = 44732.66
correct profit value (SP-CP)= 43212.43
Hi Alex, thank you so much for your tutorial. I actually made it to the last step but the IF statement is giving me an #error on my order_size column and ‘cannot find table ‘big order’
I entered this:
IF(‘table name’[Units Sold]>25, ‘Big Order’, ‘Small Order’)
Did I miss anything? Like spacing etc? Thank you
You have to use " instead of ' for values.
@@umairkhan7324 Thank you
@@umairkhan7324 Thank you
is it a bad practice that I did this
Profit = (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost])) * [Total product sold]
instead of this
Profit = (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost])) * SUM('Apocolypse Sales'[Units Sold]?
Hey Alex, I was doing the math and the value of the profit per item is WRONG. It is multiplying [price - cost] of the product 10001 by the total of units sold (3001), therefore the value of 59k of profit. The corret value is multiplied by 390 (Total amount sold of the item 10001) which result a total of 6754,8.
I tried to fix it in my Power BI but, since I'm learning, I couldn't find a solution. If you ever see this comment or anyone who knows how to fix it, please teach me!
The numbers that I got at 6:33 are - and I don't know what the problem is
Hey there, god bless your efforts in this channel.
I have only a simple enquiry as an new sql learner.
!= Are these two comparison operators mean the same thing in sql syntax?
Thanks for taking care of this.
Hey, if you take literally the question you asked here and copy paste it to google, you can get an answer instantly instead of waiting for another person to respond. :D
❤❤❤
5:40 From here on the video is wrong, all the formulas indicated are incorrect
Iluv your videos but using DAX only for count is redundant and the example of SUMX looks great on the video but impossible to do it on PBI :(
Iam unable to download the dataset, can somebody please help me out
In my profit_column_sumx the values are showing in negative numbers
Same problem . Also my Unit sold column is not showing in the suggestion
Hi Alex, after the getting the Profit.. I placed the customer over the profit but it didn’t split the profit between the customers.. it was showing the total figure in front of all the customers.. What to do anybody😢😢
Can someone please explain why in the SUMX experession:
" Profit_Column_sumX = SUMX( 'Apocolypse Sales', ( ('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold] ) )"
We used "Apocolypse Sales" in this part ( SUMX(, ))and not "Apocolypse Store", even though we are adding the column to "Apocolypse Store"??
He totally made a mistake there. The right way should be get the result for each each and add them together.
as a tableau developer, i prefer tableau calculated field tbh
Here is the profit formula i used but it gave me negative answers like '-200' I don't know if my formula is wrong or ?? Profit = SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost]) * SUM('Apocolypse Sales'[Units Sold]).
Please let me know the error if you find any. Thanks
for anyone having the same problem in the order of operations, multiplication, and division come before addition and subtraction so you have to separate the subtraction from the multiplication with parenthesis
Profit = (SUM('Apocolypse Store'[Price]) - SUM('Apocolypse Store'[Production Cost])) * SUM('Apocolypse Sales'[Units Sold])
@@mounirasavadogo1013 Thank you 😊
I can't view the tables in Table View. In the model view everything is visible but not in the Table view. Can someone help, please? Do you know how I can fix that?
Could anyone in this community help me understand SUMx function and difference between sum and sumx. I couldn't able to understand from that example
Hey, why I can't make the Units Sold By Year and it only does :"Sum of Units Sold By Year", I tried to don't summarize but it didn't help. now it counts it. Please help.
hello
I Used The Sumx Just LIke The tutorial You'v been learn But The Power Bi Throws An Error About You Cant Use The UnitSold Column Of The Second Dataset May Any One Help Me ???
The Full Error Is :
' single value for column in table cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregator such as min, max, count, or sum to get a single result. '
How come am getting a profit as a negative?
use SUMX('Apocolypse Sales',('Apocolypse Store'[Price] - 'Apocolypse Store'[Production Cost]) * 'Apocolypse Sales'[Units Sold])
I do not understand why you have used (SUM(Price) - SUM(Productioncost))*SUM(Unitssold). Shouldn't it be SUM((Price - Productioncost)*Unitssold). Cause profit will be different for each product. I believe what you did what calculated entire profit for one unit and multiply it with the total units of all products bought by the customer.
I see it in the same way... Product profit depends on number of units sold of each product and not on total number of units sold...
@@jf_romo8113 You wrote this a long time ago, but I will respond in case someone else has the same question. I made some tests in Excel, and it seems like this UA-camr has done the math incorrectly. The only way to get the numbers in his table, is if you first take the sum of ALL prices and subtract that by ALL product costs, then multiply by the number of products that the customer bought. This makes zero sense, of course, because the customer did not buy all products, and certainly did not buy the same amount of each product.
Alex