Quarterly Average over Weekly Average

[gra_wang]

I'm trying to create a line and clustered chart where the clusters/bars show weekly averages while the lines show quarterly averages.

Here is the measure I used to try to get a quarterly average:

Quarterly Average Talk Time =
AVERAGEX(
    SUMMARIZE(subAgentCall,subAgentCall[Quarter],subAgentCall[skill_name],subAgentCall[Call Type],"Average Talk Time",
    (SUM(subAgentCall[SUM TT])/SUM(subAgentCall[Calls Handled]))/60),[Average Talk Time])

Problem is (as indicated by the picture above), all that happens is the line matches the weekly average. Is it possible to create a measure that calculates strictly at the quarterly level and utilize it on a graph using weekly intervals?

[Ashish_Mathur]

Hi,

I am just unable to understand your requirement.  If there are data on the X-axis, then how can you show a line chart for quarters?

[gra_wang]

I am trying to get the bars to indicate average talk time on a weekly level (which it is already doing), and I am trying to get the line to show the average on a quarterly level. The idea is to compare weekly to quarterly averages.

Here is the raw data:

For weekly average, I just used a simple measure:

Average Talk Time = (SUM(subAgentCall[SUM TT])/SUM(subAgentcall[Calls Handled]))/60

then I graphed the measure with the x-axis based on the Week column indicated in the picture above.
Now I want to have the line show quarterly average so I am looking for a measure similar to Average Talk Time, but calculates it on a quarterly level. If a measure is not the best practice, then I am open to other suggestions. Thanks for your response. Hope this clarifies my requirement.

[Ashish_Mathur]

Hi,

I cannot appreiate your requirement.  If weeks are shown on the X-axis then how can the line show quarterly averages?  Someone else will help you.

[gra_wang]

Here's me doing this in Excel (different data set but the same concept):



Hopefully that helps?

[v-chuncz-msft]

@gra_wang,

You may use ALLSELECTED Function.

ALLSELECTED ( subAgentCall )

[gra_wang]

I created a measure using ALLSELECTED as you suggested:

Quarterly Average Talk Time =
CALCULATE(
    SUMX(
        SUMMARIZE(subAgentCall,subAgentCall[Quarter],subAgentCall[skill_name],subAgentCall[Call Type],"Quarterly Talk Time",
        (SUM(subAgentCall[SUM TT])/SUM(subAgentCall[Calls Handled]))/60),[Average Talk Time]),ALLSELECTED(subAgentCall[Quarter]))

The result is this:

As you can see, I get an abnormally high value for the Quarter Average and it appears to be fixed (the quarterly average should change after 9/24/2017). Can you be more specific as to how I should use ALLSELECTED()?

[v-chuncz-msft]

@gra_wang,

Drag [Quarter] to Shared axis as well, and use ALLSELECTED ( subAgentCall[Week] ).

[gra_wang]

I have modified the formula for the measure:
Quarterly Average Talk Time =
CALCULATE(
    SUMX(
        SUMMARIZE(subAgentCall,subAgentCall[Quarter],subAgentCall[skill_name],subAgentCall[Call Type],"Quarterly Talk Time",
        (SUM(subAgentCall[SUM TT])/SUM(subAgentCall[Calls Handled]))/60),[Average Talk Time]),ALLSELECTED(subAgentCall[Week]))

and I dragged [Quarter] into the shared axis. Unfortunately, I am still not getting the desired result:

The line values are different, but I am still getting a constant line instead of a line that changes as the quarter changes.

[v-chuncz-msft · Accepted solution]

@gra_wang,

Drag [Quarter] on top of [Week], and select the third icon "Expand all down one level in the hierarchy" in the top left corner of the visualization.

[gra_wang]

Thanks for your help thus far.

Is there a way to hide the quarter axis label?

Here is how my x-axis labels look right now: