Forum Discussion

SimonJacobs's avatar
SimonJacobs
Icon for Helper I rankHelper I
9 years ago

Calculating Averages when using a Calculate and Filter Measure

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:

 

CountryBrandData TypeMetricValue
UKBrand 1Brand AwarenessAware25%
UKBrand 2Brand AwarenessAware17%
UKBrand 3Brand AwarenessAware54%
UKBrand 4Brand AwarenessAware35%
UKBrand 1Brand AwarenessFamiliar20%
UKBrand 2Brand AwarenessFamiliar12%
UKBrand 3Brand AwarenessFamiliar49%
UKBrand 4Brand AwarenessFamiliar30%
USBrand 1Brand AwarenessAware15%
USBrand 2Brand AwarenessAware5%
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

Need Help

8 Replies

  • mfc1982's avatar
    mfc1982
    Frequent Visitor
    9 years ago

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

      • v-micsh-msft's avatar
        v-micsh-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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