How to: Two overall average rows on a crosstab, one with slicer and one without

[robblob]

I have a request to have two summary rows on a crosstab; one with a slicer and one without.  The crosstab is built from a DMR if that helps or matters. 

Use case: There are several metrics related to members of teams that have phone interactions.  The user wants a summary row of averages based on whether the user was present and made a phone interaction indicating that they worked that day.  They don't want to penalize the metric by looking at the total number of members from the team table, but only those that made a call.

For the second summary row of averages, they WANT to see the same metrics with everyone on the team included whether they made a call or not. 

Solution:  I'm able to account for those that made an interaction by adding a slicer to the query feeding the crosstab where interaction > 0, but I'm not sure how to add a second average row that ignores the slicer on the query. 

Is there a simple solution to creating this additional overall average row that I'm just too inexperienced to realize?  I want to avoid creating an additional crosstab if possible.

[MFGF]

I have a request to have two summary rows on a crosstab; one with a slicer and one without.  The crosstab is built from a DMR if that helps or matters. 

Use case: There are several metrics related to members of teams that have phone interactions.  The user wants a summary row of averages based on whether the user was present and made a phone interaction indicating that they worked that day.  They don't want to penalize the metric by looking at the total number of members from the team table, but only those that made a call.

For the second summary row of averages, they WANT to see the same metrics with everyone on the team included whether they made a call or not. 

Solution:  I'm able to account for those that made an interaction by adding a slicer to the query feeding the crosstab where interaction > 0, but I'm not sure how to add a second average row that ignores the slicer on the query. 

Is there a simple solution to creating this additional overall average row that I'm just too inexperienced to realize?  I want to avoid creating an additional crosstab if possible.

Hi,

As you are using a DMR package, you have access to a range of dimensional functions and expressions that can help. For example, you could remove the slicer filter and code each summary as a dimensional expression. In the summary for people who worked, you can use a filter() function to focus on just those members with an interaction > 0,
eg average(currentMeasure within set filter([your team members level], [interaction] > 0))
and in the other summary you could average across the entire set of members
eg average(currentMeasure within set [your team members level])

Cheers!

MF.

[robblob]

Hi,

As you are using a DMR package, you have access to a range of dimensional functions and expressions that can help. For example, you could remove the slicer filter and code each summary as a dimensional expression. In the summary for people who worked, you can use a filter() function to focus on just those members with an interaction > 0,
eg average(currentMeasure within set filter([your team members level], [interaction] > 0))
and in the other summary you could average across the entire set of members
eg average(currentMeasure within set [your team members level])

Cheers!

MF.

I took your suggestion and modeled it into the crosstab, but i'm getting identical results for both expressions.  I've provided a screenshot of the outputs and the expressions feeding them (blurred out some details), so hopefully someone can tell me where I've messed up.

[MFGF]

I took your suggestion and modeled it into the crosstab, but i'm getting identical results for both expressions.  I've provided a screenshot of the outputs and the expressions feeding them (blurred out some details), so hopefully someone can tell me where I've messed up.

What do you have displayed in the rows of your crosstab? Is it the set of members comprising those who worked? If so, how are you deriving this set eg do you have any filters in your query?

MF.

[robblob]

What do you have displayed in the rows of your crosstab? Is it the set of members comprising those who worked? If so, how are you deriving this set eg do you have any filters in your query?

MF.

The rows in the crosstab are the employees and the number of interactions that they made, if any.  It's my employee hierarchy since the report starts 2 levels up from what is currently displayed.  When the report runs, it starts at a market level, then drills to team level, then drills to employee level which is what is used in the expression to evaluate whether the total interactions are >0. 

The only filters I have on the query are date filters.  Nothing related to any of the metrics or employees. 

[MFGF]

The only filters I have on the query are date filters.  Nothing related to any of the metrics or employees.

Are these detail filters or slicer filters, or are they implied as part of a dimensional expression (eg part of a tuple)?

MF.

[robblob]

Are these detail filters or slicer filters, or are they implied as part of a dimensional expression (eg part of a tuple)?

MF.

They are both detail filters for prompts on the page.  One for time periods and one for date, and both are pulled from date hierarchy in DMR that are based on date dimension.

[MFGF]

They are both detail filters for prompts on the page.  One for time periods and one for date, and both are pulled from date hierarchy in DMR that are based on date dimension.

Hi,

I'd strongly recommend not using detail filters with a dimensional package. You can get some very odd and misleading results. Assuming the periods and dates are not being displayed in your crosstab, slicer filters using dimensional expressions would be the way to go, eg

[your Periods level] -> ?Your Periods Parameter?

The odd-looking -> above is a dimensional operator. If you need to be able to choose more than one period you can encapsulate the expression in a set() function.

I would fix this first, then we can review whether the summaries are still misbehaving.

Cheers!

MF.

[robblob]

Hi,

I'd strongly recommend not using detail filters with a dimensional package. You can get some very odd and misleading results. Assuming the periods and dates are not being displayed in your crosstab, slicer filters using dimensional expressions would be the way to go, eg

[your Periods level] -> ?Your Periods Parameter?

The odd-looking -> above is a dimensional operator. If you need to be able to choose more than one period you can encapsulate the expression in a set() function.

I would fix this first, then we can review whether the summaries are still misbehaving.

Cheers!

MF.

Ok, you've definitely got me to the point of working through this and things are kinda starting to click, but I've hit another snag. 

The average calculate member expression I had previously was always returning the average of the employee member level, which makes sense now that I am beginning to wrap my head around these expressions.  Anyway, I changed the expression to average(currentMeasure within set currentMember([UserHierarchy])).  This now gives me the correct average that I want to see as a whole at any level. 

I'm working in a new report to get away from the detail filters as you suggested, and I got the prompt working perfectly within a slicer.  What I'm having trouble figuring out is where in the above expression to add the 'filter' function for the employee member for interaction >0.  I've tried several variations, but can't get it to validate.

[MFGF]

Ok, you've definitely got me to the point of working through this and things are kinda starting to click, but I've hit another snag. 

The average calculate member expression I had previously was always returning the average of the employee member level, which makes sense now that I am beginning to wrap my head around these expressions.  Anyway, I changed the expression to average(currentMeasure within set currentMember([UserHierarchy])).  This now gives me the correct average that I want to see as a whole at any level. 

I'm working in a new report to get away from the detail filters as you suggested, and I got the prompt working perfectly within a slicer.  What I'm having trouble figuring out is where in the above expression to add the 'filter' function for the employee member for interaction >0.  I've tried several variations, but can't get it to validate.

Hi,

average(currentMeasure within set currentMember([UserHierarchy])) doesn't really make sense to me. As a crosstab is being built, the report will iterate through the chosen members for each level or hierarchy one at a time, as each row, column and intersection of the crosstab is being constructed. The currentMember() function allows you to access the specific member of the hierarchy being used at that particular point in the iteration. It doesn't make sense to try to aggregate a measure across a single member like this, as it's a single member, not a set? Can you explain exactly what you need to do?

MF.

[robblob]

Hi,

average(currentMeasure within set currentMember([UserHierarchy])) doesn't really make sense to me. As a crosstab is being built, the report will iterate through the chosen members for each level or hierarchy one at a time, as each row, column and intersection of the crosstab is being constructed. The currentMember() function allows you to access the specific member of the hierarchy being used at that particular point in the iteration. It doesn't make sense to try to aggregate a measure across a single member like this, as it's a single member, not a set? Can you explain exactly what you need to do?

MF.

I apologize, as I've probably done a terrible job of explaining this in words, so I created an illustration as an attachment to help explain what I'm trying to do.

The "Avg" summary row is what I'm going to get by simply telling the crosstab to create a summary row with aggregate type of avg.  My above explanation that was confusing was just me trying to understand what the expression was doing.

The "Avg unfiltered" row is what I'm trying to do that I can't figure out.  At the very bottom level of the attachment where you see the breakdown by employee, you can see that not all of the employees had an interaction.  The "Avg" summary row handles this how you would expect it to by only including those that made a call.  However, my users are wanting to see the same average, except including all possible employees in the average function whether they had an interaction or not (thus driving down the average). 

I need the "Avg unfiltered" row to roll-up at every level within the User Hierarchy.  The user wants to see the averages at every level of the crosstab/hierarchy, one only based on those with interactions and one based on those with and without.

Hopefully this makes more sense, and I apologize again for the confusion. 

[MFGF]

I apologize, as I've probably done a terrible job of explaining this in words, so I created an illustration as an attachment to help explain what I'm trying to do.

The "Avg" summary row is what I'm going to get by simply telling the crosstab to create a summary row with aggregate type of avg.  My above explanation that was confusing was just me trying to understand what the expression was doing.

The "Avg unfiltered" row is what I'm trying to do that I can't figure out.  At the very bottom level of the attachment where you see the breakdown by employee, you can see that not all of the employees had an interaction.  The "Avg" summary row handles this how you would expect it to by only including those that made a call.  However, my users are wanting to see the same average, except including all possible employees in the average function whether they had an interaction or not (thus driving down the average). 

I need the "Avg unfiltered" row to roll-up at every level within the User Hierarchy.  The user wants to see the averages at every level of the crosstab/hierarchy, one only based on those with interactions and one based on those with and without.

Hopefully this makes more sense, and I apologize again for the confusion.

Hi,

I'm not quite understanding how you arrive at the unfiltered averages at the Market and Team levels? What is the logic you use (for example) for delivering the 120 figure at the Market level? Does it involve using the descendants at the Employee level somehow?

MF.

[robblob]

Hi,

I'm not quite understanding how you arrive at the unfiltered averages at the Market and Team levels? What is the logic you use (for example) for delivering the 120 figure at the Market level? Does it involve using the descendants at the Employee level somehow?

MF.

The "Avg unfiltered" numbers at the Market and Team levels are just placeholders since I intended for the Employee level to communicate the point.  I omitted interactions explicitly at the Employee level for a visual representation of what I'm needing, but the same concept applies at every level.  If somehow a Market or Team didn't have interactions, I would want the "Avg unfiltered" summary to handle it in the same manner. 

[MFGF]

The "Avg unfiltered" numbers at the Market and Team levels are just placeholders since I intended for the Employee level to communicate the point.  I omitted interactions explicitly at the Employee level for a visual representation of what I'm needing, but the same concept applies at every level.  If somehow a Market or Team didn't have interactions, I would want the "Avg unfiltered" summary to handle it in the same manner.

So you could have Market or Team members without a measure value? Interesting.

For the filtered average, you could try this:
total(currentMeasure within set [your Employees level]) / count(currentMeasure within set [your Employees level])

And for the unfiltered average you could try:
total(currentMeasure within set [your Employees level]) / total(1 within set [your Employees level])

Obviously the count of measures within the set will be the count of members which have a measure value, and the total(1...) should return the number of members regardless of whether they have a measure value.

Does this work as an approach?

MF.