TopN + Others - Circular reference

Question

Hi all,I've been struggling with a formula for a few days now so I'm reaching out for help.&nbsp; I have read multiple articles and posts but still can't figure out what's causing the circular reference error in this statement.&nbsp; I'm following an example I found online for creating Top N and "other" for customer tickets.&nbsp; Below is my statement -CSTs TopN+Others = VAR RankCSTVolume = RANKX(ALL('CSTs from 2018 to current'),[Service Tickets Total],,DESC)returnif (RankCSTVolume&lt;=10, 'CSTs from 2018 to current'[Cat2andCat3], "Others")&nbsp;The only calculated field is 'Service Tickets Total' -Service Tickets Total = CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;SUM('CSTs from 2018 to current'[Service Tickets]),&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED('CSTs from 2018 to current'[Service Tickets]))Any ideas appreciated!&nbsp; Thanks!

amitchandak · Answer

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&lt;=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&lt;=10, 'CSTs from 2018 to current'[Cat2andCat3], "Others")

anonymous · Answer

Thanks amitchandak&nbsp;... I think it's close.&nbsp; I'm still getting an error on the last statement -if (RankCSTVolume&lt;=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.&nbsp; It seems to only accept a caclulated field.

amitchandak · Answer

Try like.
if (RankCSTVolume&lt;=10, firstnonblank('CSTs from 2018 to current'[Cat2andCat3],blank()), "Others")
&nbsp;
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-Difference-Across/ba-p/934397#M451

anonymous · Answer

amitchandak&nbsp;thanks for your help.&nbsp; Unfortunately when I replaced the 'if' statement I recieved a circular reference error.&nbsp; 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.&nbsp; I also tried to break up the statements into two statements thinking that might address the circular reference but no luck.&nbsp; Open to any other ideas.&nbsp; Below is the last statement I used.&nbsp;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&lt;=10, firstnonblank('CSTs from 2018 to current'[Cat2andCat3],blank()), "Others")&nbsp;

amitchandak · Answer

See if you can replicate your issue on this file
https://www.dropbox.com/s/drp7gscx3w1liyc/sales_analytics_cumm.pbix?dl=0

Forum Discussion

TopN + Others - Circular reference

10 Replies

Related content

Circular Reference error with no circular reference

Circular reference error

Circular dependency

How to fix Circular reference in Data Relationship

Circular Reference Error help