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.
Hello Power BI Gurus,
I am stuck with an issue where I have created a few measures and one of the measure is erroring out because of circular reference. It have tried many alternatives but haven't been successful. It might be pertty trivial issue and I may have been overthinking.
Requirement : I have a data set about agents completing cases everymonth and getting paid in Tiers depending upon the cases they complete. If they don't reach threshold for a Tier , the cases get carried to next month and evaluated with next month's cases .Tiers defined are 40,80 and 120 , so if an agent completes 37 cases,all of his cases get processed next month and if his completes 45 , he gets paid for 40 and 5 of his cases move to next month & so on.
Here is the data set and calculations I have iused to implement the logic where 4th measure errors out with circular reference. Any ideas on how I can resolve this as I have tried quite a few things but nothing seemed to have helped resolving this.Thanks for your help in advance
Table columns | Table columns | Table columns | Table columns | Measures | Measures | Measures | Measures | Measures |
Agent | Month | Cases solved | Carry forward to next month | Pending from Last month | Total cases this month | Carry forward from Previous Month | Tier | |
AB | Jan | 20 | 20 | 7 | (20+7) =27 | 7 | No Payment | |
AB | Feb | 30 | 30 | 20 | 27+30=57 and not 20+30 as 27 from previous month is below threshold and hence carried forward | 27 | Tier1 | |
AB | Mar | 40 | 0 | 30 | 40+17 from prev month (As 57 crossed tier 1 threshold hence remaining 17 would flow through to this month | 17 | Tier1 | |
AB | Apr | 60 | 20 | 0 | 60+17 =77 to be processed which falls short of 80(Tier2 threshold) and hence 37 would carry forward | 37 | Tier1 | |
AA | Jan | 30 | 30 | 20 | 30+20=50 ,40 to be processed and 10 carry forward to next month New agent counter restarts from first month of service for agent) | 10 | Tier1 | |
AA | Feb | 80 | 0 | 30 | 90 | 10 | Tier2 | |
AA | Mar | 90 | 10 | 0 | 90+10 | 20 | Tier2 | |
AA | Apr | 46 | 6 | 10 | 66 | 26 | Tier1 |
Below are the measure calculations :
1)
PendingLastmonth = CALCULATE(sum(Monthlypayments[Carry forward to next month]),PREVIOUSMONTH(Calendar_dim[Date].[Date]))
2) Carry Forward from previous month = CALCULATE([Carried to next month],PREVIOUSMONTH(Calendar_dim[Date].[Date]))
3)
Carried to next month = if([totalcase]<40, [Totalcase], (if([Totalcase]>=40
&& [Totalcase]<80, [Totalcase]-40,(if(([Totalcase])>=80 && [Totalcase]<120,
[Totalcase]-80,(if([Totalcase]>=120, [Totalcase]-120,0)))))))
4)
Totalcase = sum(Monthlypayments[Total case])+[Carried from previous month]
Solved! Go to Solution.
@Anonymous , this sequence of measure is not correct,
Create a cumulative measure and try like
Cumm cases last month = CALCULATE(sum(Monthlypayments[Total case]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,year)))
cases this month = sum(Monthlypayments[Total case])
paid this month = Quotient(mod([Cumm cases last month],40) + [Cumm cases this month], 40)
@Anonymous , this sequence of measure is not correct,
Create a cumulative measure and try like
Cumm cases last month = CALCULATE(sum(Monthlypayments[Total case]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,year)))
cases this month = sum(Monthlypayments[Total case])
paid this month = Quotient(mod([Cumm cases last month],40) + [Cumm cases this month], 40)
Hi Amit, Thanks for your previous solution. There is one more issue on top of the above, that I got stuck with and I am not able to get it working. If an employee's total is more than 120 cases then we need to carry forward the entire amount forward rather than a mod of 40, For eg, if the total is 165 then we should carry forward 45 and not 5 . I have tried using If condition to divide by 40 when <120 and divide by 120 and use that to associate it to the next month's row .
Carry forward t o next month does work well but when I try to use in the next row using previous month or date add it doesn't quite me the correct results
That's Brilliant Amit! Thanks a lot for this quick solution! I was struggling to get the Cumulative sum working as I use using Max in the data comparison rather than MAxx function. This really helped me get through the blocker. Thanks again for your help
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 |
---|---|
43 | |
22 | |
21 | |
16 | |
15 |
User | Count |
---|---|
46 | |
31 | |
29 | |
18 | |
18 |