Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I was trying to do "Top N + others", tried many options from different community and finaly only one solution worked for me from below link,
https://www.goodly.co.in/top-n-and-others-power-bi/
Now problem is total is not considering "Others" - it should be 29M instead of 4M.
Can anyone suggest how to fix this "Total"..
Here is the DAX used ,
attached two solutions PBIX files I used here,
https://we.tl/t-xIzgNWAzme
Thanks in advacne
Solved! Go to Solution.
This is because your CurrentProd is blank for the Total case (since multiple Products are in scope).
Try updating the RETURN part of your DAX to this:
RETURN
IF(
ISBLANK ( CurrentProd ),
TopProdSales + OtherSales,
IF (
CurrentProd <> "Others",
TopProdSales,
OtherSales
)
)
Hmm. Maybe the bottom products don't have any sales, so they're returning blank.
This is because your CurrentProd is blank for the Total case (since multiple Products are in scope).
Try updating the RETURN part of your DAX to this:
RETURN
IF(
ISBLANK ( CurrentProd ),
TopProdSales + OtherSales,
IF (
CurrentProd <> "Others",
TopProdSales,
OtherSales
)
)
Thank you so much @AlexisOlson , it is adding up now.
Any option to do the same for "Bottom" N instead of "Top" for the same table - couldn't find a solution
You can use the opposite sorting in the TOPN function. That is, use ASC rather than the default DESC.
Tried it but getting only "Others" total.
Top N Sum Sales v2 =
VAR TopNSelected = SELECTEDVALUE('TopN Selection'[Value])
VAR TopProdTable =
TOPN(
TopNSelected,
ALLSELECTED('Pseudo Prod Table'),
[Total Sales],ASC
)
VAR TopProdSales =
CALCULATE(
[Total Sales],
KEEPFILTERS( TopProdTable )
)
VAR OtherSales =
CALCULATE(
[Total Sales],
ALLSELECTED('Pseudo Prod Table')
) -
CALCULATE(
[Total Sales],
TopProdTable
)
VAR CurrentProd = SELECTEDVALUE('Pseudo Prod Table'[Product])
RETURN
IF(
ISBLANK ( CurrentProd ),
TopProdSales + OtherSales,
IF (
CurrentProd <> "Others",
TopProdSales,
OtherSales
)
)
here is the output
Hmm. Maybe the bottom products don't have any sales, so they're returning blank.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |