Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

TopN + Others - Circular reference

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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")

 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

        

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

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