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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Dunner2020
Post Prodigy
Post Prodigy

ALL function does not remove filter

I have call center data. The data has got 15 mins windows and contains information about call handled by agents. I want to calculate the Average no of calls handled by all agents per hour and Average no of calls handled by an agent per hour. 

Following are the measures:

 

Avg contact handled by  an agent per hour =
   Averagex(
SUMMARIZE(ADDCOLUMNS(AGENTS_15MIN_VIEW, "Hour",HOUR(AGENTS_15MIN_VIEW[Time]), "Week",WEEKNUM(AGENTS_15MIN_VIEW[Start Date])), [Hour], [Week], "Calls attended", SUM(AGENTS_15MIN_VIEW[Contacts handled incoming]) ),
[Calls attended] )
 
Avg contact handled by all agents per hours =
AVERAGEX(
SUMMARIZE(
AGENTS_15MIN_VIEW,
'Dates'[Week Number], 'Time'[Hour Number],
"Avg", CALCULATE( DIVIDE(SUM(AGENTS_15MIN_VIEW[Contacts handled incoming]),DISTINCTCOUNT(AGENTS_15MIN_VIEW[Agent Name])),ALL('Agents Name'[Agent Name]))
),
[Avg]
)
 
The problem is in the second measure. When I don't select any specific agent-name from drop down list, it shows the average call handled by all agents. However, as soon as I select any agent from drop-down list it shows the average of the selected agent. I just want the measure to show the overall average of agents and not show the average of a specific agent. I used the ALL function to remove any filter but it looks like it did not work. Any help to fix the issue would be appreciated.
 
Sample here
3 REPLIES 3
lbendlin
Super User
Super User

that would be a question for your data model.  Please provide some more details.

@lbendlin , here is more details.

 

I have 4 tables: Agents Name, AGENTS_15MINS_VIEW, Dates, and Time. AGENTS_15MINS_VIEW table contain information related to call handled by agents in 15 mins intervals. The table has information like how many calls handled in 15 mins interval, date, and agent name (i.e. who handled call). AGENTS_15MINS_VIEW is fact table which has many to one relationships with other dimensions tables (i.e. Agent Name, dates, and time). I have drop down list which contains the name of agents that comes from Agent Name table. I created two measures. One measure should calculate the average call handled by selected agent per hour and second measure should calculate the average call handled by all agents per hour. Measures are defined as follow:

Avg contact handled by  selected agent per hour =

   Averagex(

SUMMARIZE(ADDCOLUMNS(AGENTS_15MIN_VIEW, "Hour",HOUR(AGENTS_15MIN_VIEW[Time]), "Week",WEEKNUM(AGENTS_15MIN_VIEW[Start Date])), [Hour], [Week], "Calls attended", SUM(AGENTS_15MIN_VIEW[Contacts handled incoming]) ),

[Calls attended] )

 

Avg contact handled by all agents per hours =

AVERAGEX(

SUMMARIZE(

AGENTS_15MIN_VIEW,

'Dates'[Week Number], 'Time'[Hour Number],

"Avg", CALCULATE( DIVIDE(SUM(AGENTS_15MIN_VIEW[Contacts handled incoming]),DISTINCTCOUNT(AGENTS_15MIN_VIEW[Agent Name])))

),

[Avg]

)

 

The second measure shows average call handled by all agents per hour until we select specific agent name from agent drop down list. As soon as we select agent name from drop down list, it shows the same calculation as the first measure shows i.e. average call handled by selected agent. I want second measure to show the average call handled by all agents per hour irrespective of agent name selection from the drop down list. Could you please help me in fixing the issue?

Sorry I should have been more specific. Please show a sanitized version of your data model. The data model connections impact the scope of the filter functions.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.