Hello, I was hoping someone could help explain how to add a column that gives me the name of product within the area that has the max quantity.
I found i can use DAX formulas to show the MAXX value for the row within a row.
Is there a way that I can get excel to give me the name of the product that has this max quantity?
So for like South, it will give me Blue when it is collapsed.
Capture.PNG
Hi odubby,
If you attach the sample workbook instead of a picture we can show you how. Pivots allow you to do a "Top 10 Filter" but in your case you would want a Top 1. Then you might need to do a different design of your data to show it how you want.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Hi
Need two measure for for this (assume table name is Table1)
TotQty:=sum(Table1[Quantity])
Max Product:=CALCULATE(VALUES(TABLE1[Product]),TOPN(1,TABLE1,[TotQty]))
Hi
pls change the formula to this Max Product:=CALCULATE(VALUES(TABLE1[Product]),FIRSTNONBLANK(TOPN(1,TABLE1,[TotQty]),0))
otherwise error will happen when there is more than one product with highest value
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks