TopN + Others - Circular reference

[Anonymous]

Hi all,

I've been struggling with a formula for a few days now so I'm reaching out for help.  I have read multiple articles and posts but still can't figure out what's causing the circular reference error in this statement.  I'm following an example I found online for creating Top N and "other" for customer tickets.  Below is my statement -

CSTs TopN+Others =

VAR RankCSTVolume = RANKX(ALL('CSTs from 2018 to current'),[Service Tickets Total],,DESC)

return

if (RankCSTVolume<=10, 'CSTs from 2018 to current'[Cat2andCat3], "Others")

The only calculated field is 'Service Tickets Total' -

Service Tickets Total =

CALCULATE(

    SUM('CSTs from 2018 to current'[Service Tickets]),

    ALLSELECTED('CSTs from 2018 to current'[Service Tickets])

)

Any ideas appreciated!  Thanks!

[Anonymous]

Hi @Anonymous ,

If the last post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Try like

CSTs TopN+Others =
VAR RankCSTVolume = RANKX(ALLselected('CSTs from 2018 to current'),CALCULATE(
    SUM('CSTs from 2018 to current'[Service Tickets])
),,DESC)
return
if (RankCSTVolume<=10, 'CSTs from 2018 to current'[Cat2andCat3], "Others")
 
 

CSTs TopN+Others =
VAR RankCSTVolume = RANKX(ALL('CSTs from 2018 to current'),CALCULATE(
    SUM('CSTs from 2018 to current'[Service Tickets])
),,DESC)
return
if (RankCSTVolume<=10, 'CSTs from 2018 to current'[Cat2andCat3], "Others")

[Anonymous]

Thanks @amitchandak ... I think it's close.  I'm still getting an error on the last statement -

if (RankCSTVolume<=10, 'CSTs from 2018 to current'[Cat2andCat3], "Others")

The error is - Cannot find name '[Cat2andCat3]'

Seems like a syntax error but I don't see why.  It seems to only accept a caclulated field.

[amitchandak]

Try like.

if (RankCSTVolume<=10, firstnonblank('CSTs from 2018 to current'[Cat2andCat3],blank()), "Others")

But row context is very important

Check how I have taken care of that in date diff :https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[Anonymous]

@amitchandak thanks for your help.  Unfortunately when I replaced the 'if' statement I recieved a circular reference error.  I tried to create a sample file to share with you so that you could see the data and error, but apparently this board restricts attachments.  I also tried to break up the statements into two statements thinking that might address the circular reference but no luck.  Open to any other ideas.  Below is the last statement I used.

CSTs TopN+Others = 
VAR RankCSTVolume = RANKX(ALLselected('CSTs from 2018 to current'),CALCULATE(
    SUM('CSTs from 2018 to current'[Service Tickets])
),,DESC)
return
if (RankCSTVolume<=10, firstnonblank('CSTs from 2018 to current'[Cat2andCat3],blank()), "Others")

[amitchandak]

See if you can replicate your issue on this file

https://www.dropbox.com/s/drp7gscx3w1liyc/sales_analytics_cumm.pbix?dl=0

[Anonymous]

@amitchandak   the statements works perfectly on your data!  This tells me the error has somthing to do with my data.  I'll keep digging to try and find out why. 

Items TopN+Others = 
VAR RankItemSales = RANKX(ALLSELECTED('Item'),CALCULATE(SUM('Sales'[Net Sales])),,DESC)
return
if (RankItemSales<=10,('Item'[Brand]),"Others")

[amitchandak]

You are creating a column in your data and I think we were trying for the measure. I created as measure and it worked

[amitchandak]

Create a measure or In case you want a column. Create two. Also, in case of column rank will not take the Aggregated column. Also no need of first non blank

[Anonymous]

@amitchandak  thanks will do.  When I used your link I was able to create a drop box account!  I'm a newbie so this is all somewhat new to me.  Anyhow.. I now have a link that I can share with you too.   I tried to insert the link directly into the reply but that didn't work for some reason.  Hopefully you can access this.  In the meantime, I will try to recreate the issue on your file.

https://www.dropbox.com/s/gd1pgjx82ib98ij/RankTopNandOthers.pbix?dl=0