[SOLVED] Averaging percentages across filtered groups in Power View

Averaging percentages across filtered groups in Power View

I import data into Excel using Power Query, where I add a new column to calculate a usage percent. I then want to use Power View to visualize this data. However, when applying filters on the data and wanting to view the average usage percent across filters, the results are incorrect as Power View is simply averaging the percentages, rather than calculating the totals of each parameter and applying the formula afterwards. Is there a way to write my formula, so the percentage will be calculated after filters are applied in Power View?

In my example, Usage% = Direct/(Total-Fringe).

Because the denominator is not constant, the usage percent for Group A is not simply an average of the Usage% cells assigned to Group A. The average of Usage% cells in group A is 75%, but the correct usage percent calculated for the Group A using parameter totals is 98/(168-40)= 76.56%. Power View shows the incorrect 75%, as it is simply averaging the cells that correspond to Group A.

I would like to use the filters in Power View to view charts showing usage percent at various levels, including Group and Division, along with other information not shown in the example.

 ID Group   Division Direct Total   Fringe         Usage%
1   A       AA      40      40          0           1.00
2   A       AA      20      40          10          0.67
3   A       AB      18      40          15          0.72
4   A       AB      20      48          15          0.61
5   B       BA      40      40          0           1.00
6   B       BA      18      40          12          0.64
7   B       BB      12      40          20          0.60
8   B       BB      40      48          0           0.83

Solution

Create Usage % as a Measure in your Data Model, instead of as a column:

Usage % = 
    DIVIDE (
        SUM ( MyTable[Direct] ),
        SUM ( MyTable[Total] ) - SUM ( MyTable[Fringe] ),
        BLANK()
    )