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

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

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.

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