Forum Discussion

SimonJacobs's avatar
SimonJacobs
Helper 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 controlle...
Need Help
SimonJacobs's avatar
SimonJacobs
Helper I
9 years ago

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's avatar
v-micsh-msft
Microsoft Employee
9 years ago

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's avatar
    SimonJacobs
    Helper I
    9 years ago

    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's avatar
      v-micsh-msft
      Microsoft Employee
      9 years ago

      Hi SimonJacobs,

       

      My bad.

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

      Regrads

       

      • SimonJacobs's avatar
        SimonJacobs
        Helper I
        9 years ago

        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.