I think you could do a bit more with this function, I find it one of the most confusing, especially regarding order and ties, which always remind me of the way gold ties are decided and just as confusing.
Hi, Can You. Make ABC analysis in category wise, Subcategory, location wise based on sales value or average sale value (data will be vertically for instance one item's sales in different date, location, category, Subcategory) abc calculation should be on the basis of Category & subcategory
Hello Goodly. I am having lots of trouble is using TOPN to get the top 10 inventory items for each product category. All inv. items belong to a relevanf product category. Can you help. Eliza
Thanks for the video! It looked very simple the way you explained it but when I tried the similar function on a very simple Sales data, it produces an error which says like this... "The expression refers to multiple columns, Multiple columns cannot be converted to a scalar value." The TopN function I used is this... Top Sale = TOPN(1,Sales,[Total Sales]) Where [Total Sales] measure have the definition like this... Total Sales = SUM(Sales[Sales]) Link to the file is as below... drive.google.com/file/d/1Jgl1y4Xkhcaq9LUG4r0Na5d1UEZCGFIh/view?usp=sharing It would be great if you can let me know what wrong I am doing. Thanks!
@@GoodlyChandeep I appreciate your reply, thanks! This produces no error however when i add dates and Top Sale measure in a Matrix, it returns all the rows and if i add a card with Top Sale is returns the column Total. However the question remains the same that why the measure you showed in the video didn't work for me in the first place while it worked perfectly fine for you? Could you please check the file (I shared the file link in my original reply) and confirm if I am getting the correct output? I also think this would be a great topic for your next video where you can explain the reason behind this unexpected error so that it would be helpful to many of the users who are following your videos and trying to learn Power BI Dax Functions. Thanks again for your reply.
@@sktneer If you revise the DAX Code, you'll get the best selling day Top Sale = CALCULATE([Total Sales],KEEPFILTERS(TOPN(1,ALL(Sales),[Total Sales]))) Drag the date from the sales table and Top Sale Measure in a Table
@@GoodlyChandeep Yep, that produced the desired output. Btw please let me know if you ever find out the reason why the same formula which you tried in the video didn't work for me? Actually that's something strange. I understand that TopN returns a Table not a scalar value so if I insert a New Table with the expression TOPN(1,Sales,[Total Sales]), it creates a New Table with one row with the highest sale but what happens when the same function is applied to a measure? Why didn't it work like what you showed in the video? I am confused. Thanks so much for your time and reply, appreciate a lot.
5hrs research on TH-cam. your TOPN explanation 6 mins is the best and finally i understand, thankyou
nice exxplaination i could understood easily sir thank you sir we will keepwaiting for more unknown things in power bi..
Great explanation Sir, please upload more videos for DAX ....
Best video about top n
I think you could do a bit more with this function, I find it one of the most confusing, especially regarding order and ties, which always remind me of the way gold ties are decided and just as confusing.
You can you MAX() to receive a single value form a table expression. I used what I learned to get a week high in the previous year.
how do i get the product name that has that MAX value in that row using DAX
@@lavatr8322 I would use the MAXX function. e.g. CALCULATE(MAXX({TableName},{ProductName}),{FieldName} = MAX({FieldName}))
Can I use topn as a filter along with a dax topn? Or is it considered multiple topn and thus not working?
Hi,
Can You. Make ABC analysis in category wise, Subcategory, location wise based on sales value or average sale value (data will be vertically for instance one item's sales in different date, location, category, Subcategory) abc calculation should be on the basis of Category & subcategory
+1 @chandep sir.
I want to find 1 product name that is maximum sold
I used MAX func. but how do i get the name of product that has that measure
This was great..!! Thank you :)
How to get top 5 counts from multiple columns values
How to use the same formula for worst selling day?
Hello Goodly. I am having lots of trouble is using TOPN to get the top 10 inventory items for each product category. All inv. items belong to a relevanf product category. Can you help. Eliza
I thunk this would best be solved using the RANKX() function Eliza'
Great
hi, can you do a video how you get the top 1 of the most product sold in sales table?
This should help - www.goodly.co.in/top-selling-product-analysis-in-powerpivot/
Sir how to get top 10 rows and last 2 rows in a table using dax
Sir please hindi classes also
Humble request
How to get only 2 top value and name by measure
th-cam.com/video/xVLi31mOxeo/w-d-xo.html
Thanks for the video!
It looked very simple the way you explained it but when I tried the similar function on a very simple Sales data, it produces an error which says like this...
"The expression refers to multiple columns, Multiple columns cannot be converted to a scalar value."
The TopN function I used is this...
Top Sale = TOPN(1,Sales,[Total Sales])
Where [Total Sales] measure have the definition like this...
Total Sales = SUM(Sales[Sales])
Link to the file is as below...
drive.google.com/file/d/1Jgl1y4Xkhcaq9LUG4r0Na5d1UEZCGFIh/view?usp=sharing
It would be great if you can let me know what wrong I am doing.
Thanks!
Try this measure, it should work
=CALCUALTE([Total Sales],TOPN(1,Sales,[Total Sales]))
@@GoodlyChandeep I appreciate your reply, thanks!
This produces no error however when i add dates and Top Sale measure in a Matrix, it returns all the rows and if i add a card with Top Sale is returns the column Total.
However the question remains the same that why the measure you showed in the video didn't work for me in the first place while it worked perfectly fine for you?
Could you please check the file (I shared the file link in my original reply) and confirm if I am getting the correct output?
I also think this would be a great topic for your next video where you can explain the reason behind this unexpected error so that it would be helpful to many of the users who are following your videos and trying to learn Power BI Dax Functions.
Thanks again for your reply.
@@sktneer If you revise the DAX Code, you'll get the best selling day
Top Sale = CALCULATE([Total Sales],KEEPFILTERS(TOPN(1,ALL(Sales),[Total Sales])))
Drag the date from the sales table and Top Sale Measure in a Table
@@GoodlyChandeep Yep, that produced the desired output.
Btw please let me know if you ever find out the reason why the same formula which you tried in the video didn't work for me? Actually that's something strange.
I understand that TopN returns a Table not a scalar value so if I insert a New Table with the expression TOPN(1,Sales,[Total Sales]), it creates a New Table with one row with the highest sale but what happens when the same function is applied to a measure? Why didn't it work like what you showed in the video? I am confused.
Thanks so much for your time and reply, appreciate a lot.
@@sktneer I have explained the reason in the video.
excellent bro