Calculating Averages when using a Calculate and Filter Measure

[SimonJacobs]

Hi,

I'm having trouble working with multiple measures that have diffierent filters.  I'm trying to build a dynamic dashboard that allows users to select views of data so the filters are controlled by slicers rather than being specified in the measure.  The problem is I want to create an index that divides one measure for the selected view against the average for that metric across all data for that country.

To give an example of my data:

Country	Brand	Data Type	Metric	Value
UK	Brand 1	Brand Awareness	Aware	25%
UK	Brand 2	Brand Awareness	Aware	17%
UK	Brand 3	Brand Awareness	Aware	54%
UK	Brand 4	Brand Awareness	Aware	35%
UK	Brand 1	Brand Awareness	Familiar	20%
UK	Brand 2	Brand Awareness	Familiar	12%
UK	Brand 3	Brand Awareness	Familiar	49%
UK	Brand 4	Brand Awareness	Familiar	30%
US	Brand 1	Brand Awareness	Aware	15%
US	Brand 2	Brand Awareness	Aware	5%
etc…

So for example I want to calculate awareness for Brand 1 vs. the average brand awareness across all brands in the UK.  I have a measure Percent Sum (sum(BrandTrackerData[Value])) and Percent Average (average(BrandTrackerData[Value]).  I then have a page of charts controlled by slicers to select the country, the brand and the metric.  When I chart Percent Sum and Percent Average independently they work - I have the slicer that selects the brand controlling the percent sum chart but not the percent average chart so it calculates against all brands.  However when I try to create a 3rd measure - Index = [Percent Sum] / [Percent Average] I can't see a way to filter percent sum on the selected brand but calculate percent average against all brands.

Any help would be much appreciated!

Simon

[mfc1982]

I have a similar problem, did you get an answer on this?

[SimonJacobs]

Not yet.

[v-micsh-msft]

Hi Simon,

I just tested with your data example and create the measures with the formula below:

Percent Sum := sum(BrandTrackerData[Value])

Percent Average := average(BrandTrackerData[Value])

Index := [Percent Sum] / [Percent Average]

Create a table, and some Slicer visuals in the Report View, the filter works as below.

So could you please share some screenshot regarding what you would like to achieve here?

Regards

[SimonJacobs]

Hi Michael,

Thank you for your reply.  The measures you've included look the same as what I have so I've pasted some screenshots below to clarify the issue I'm having.

The Percent Sum and Percent Average measures calculate OK because the Percent Sum is filtered based on all 4 slicers (so only based on Brand 2 below) whereas the Percent Average is not filtered on the Brand slicer and therefore calculated across Brands 1 to 3.  The issue is the Index measure - it comes out as 1 for all age groups.  I suspect this is because the chart has to either be controlled or not controlled by the brand slicer meaning that both measures are either on just the selected brand, or on all brands and thus both measures give the same number and an index of 1... Does that make sense?

In the example below what I want to see for the 16-24 index value is 0.17 (1/6)

Thanks,

Simon

[v-micsh-msft]

Hi simon,

Thanks for the posts, and apology for the late response.

Would you please share the Axis value here?

In addition, replace the index formula with the following:

Index := sum(BrandTrackerData[Value]) / average(BrandTrackerData[Value])

Verify if issue insists.

Regards

[SimonJacobs]

Thanks for the response Michael.  I tried changing the Index formula and the issue was still the same.  I'm not sure what you mean by the axis value.  Does this help?:

[v-micsh-msft]

Hi SimonJacobs,

My bad.

I mean the 16-24, 25-34, if possible, would you please share about how "Cut" is calculated?

Regrads

[SimonJacobs]

No worries.  The cuts aren't calculated the data is already aggregated into the age bands.  I've pasted a sample of the data below.