Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TJPCrabfat
Frequent Visitor

Cumulative Count with KPI

Hi, cumulative counts are covered extensively here but I can't see an answer to my specific problem.

 

I'm trying to cumulatively count the number of calls a team of agents make in a day and benchmark them against a cumulative KPI.

 

First I summarize the the number of agents per day based on the calls table.

 

Active Agents = CALCULATE(COUNTROWS(SUMMARIZE(Calls,Calls[Date],Calls[AgentID])),FILTER(Calls,Calls[Role]="SalesAgent"))
 
Then create the cumulative KPI for the calls up to yesterday and summed over "This Week"
 
Calls KPI = CALCULATE([Active Agents]*60,ALL(Dates),Dates[This Week]="This Week",Dates[Date]<TODAY())
 
This works fine but when I do something similar for the number of calls they actually make during the week the KPI only shows me the previous day's total.
 
Outbound Calls = CALCULATE(COUNTROWS(Calls),FILTER(Calls,Calls[Type]="O"),FILTER(ALLSELECTED(Dates),Dates[Date]<TODAY()))
 
Calls summarized week.PNG

 

Calls.PNG

 You can see in the images above when I inclide a date on the trend axis for the KPI the calls KPI is cumulative however the actual count of calls made is not. Removing the date gives the true cumulative figure for calls made but removes the trend axis.

 

Can someone please help?

3 REPLIES 3
stevedep
Memorable Member
Memorable Member

Hi,

You have ALLSELECTED in your formula, this might be restricting your data.

Hi,

 

Thanks for the response. I've tried a few different filters within the CALCULATE function and nothing seems to work. I've tried ALL and also tried date < MAX(date). Can't understand why it works for the "Calls KPI" measure but not for the actual count of the calls.

 

Probably should have mentioned I'm struggling to get my head around context!

 

Cheers, 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors