I'm trying to find the way to do bottom N + Others by using the below command as simple as I could;
VAR TopGPTable = TOPN(8 , ALLSELECTED(ProductWithOther_Table),[GP],ASC)
VAR TopGP = CALCULATE([GP],KEEPFILTERS(TopGPTable))
VAR OtherValues = CALCULATE([GP],ALLSELECTED(ProductWithOther_Table)) - CALCULATE([GP],TopGPTable)
VAR CurrentProd = SELECTEDVALUE(ProductWithOther_Table[ProductName])
RETURN IF (CurrentProd <> "Others", TopGP , OtherValues)
Where ProductWithOther_Table is the table with distinct of product names + Others. I think this is the same method used in this discussion
However, it returned incorrect result. The reason is that ProductWithOther_Table contained 3 products that had no prices / costs / GP. So when directly use the above method, TopGP would summed up from 3 blank GP Products , 1 blank GP Others and the other 4 Least GP Products.
Again, I tried to solve this with new method by adding the below as new table and create relationship with ProductwithOther_Table[Product Name]
BottomNandOth_Table =
VAR ProductsWithPrices = FILTER( ADDCOLUMNS( ProductWithOther_Table , "@GP" , [GP]), not ISBLANK([GP]))
VAR ProductsWithOthWithPrices = FILTER( ADDCOLUMNS( ProductWithOther_Table , "@GP" , [GP]), not ISBLANK([GP]) && ProductWithOther_Table[ProductName] <> "Others")
VAR ProductOth = SELECTCOLUMNS(FILTER( ADDCOLUMNS( ProductWithOther_Table , "@GP" , [GP]), ProductWithOther_Table[ProductName] = "Others"), "Product Name" , ProductWithOther_Table[ProductName])
VAR NSelected = SELECTEDVALUE(TopN_Slicer[TopN])
VAR BottomProduct_Table = TOPN(8, ProductsWithPrices , [GP], ASC)
VAR BottomProduct_TotalAmt = CALCULATE([GP] , KEEPFILTERS(BottomProduct_Table))
VAR OtherAmt = CALCULATE([GP],ALLSELECTED(ProductWithOther_Table))-CALCULATE([GP],BottomProduct_Table)
VAR OtherProduct_Table = ADDCOLUMNS(ProductOth , "@GP" , OtherAmt)
RETURN
UNION(BottomProduct_Table , OtherProduct_Table)
The above gives the correct result. Yet the problem incurs again when I add
VAR NSelected = SELECTEDVALUE(TopN_Slicer[TopN])
and change 8 to NSelected
It seems this method can't be turned into dynamic bottom N as tried above. I have researched from forums and youtube but still not find the solution. Mostly mentioned only bottom N without others. I am planning on applying this with dynamic topN + Others with Bottom N + Others based on multiple measures such as Sales / Cost / GP.
Could anyone help with this? Any other suggestions would be greatly appreciated. Thank you in advance.
PS.
Since I'm new to PBI, sorry if the command is messy and might make you get confused.
