divide row value by total of column including slicer selections

[heidibb]

Hello,

Here is my issue. I have a column called "Index Hits", which is dividing my Hits column by the average of the hits column. this works great if I have nothing selected in my slicers.

Once i select anything in my slicers, the Index Hits column is not recognizing the filtered value and using the overall average as the denominator vs. the filtered overall average.

For example:

Data with nothing selected in slicers. Average is 46.97 and the Index Hits column will divide the Hits/average(Hits)

If I make selections in my slicers, the denominator in this example should be 82.34, but it's still using 46.97 for some reason. First row should be 522/82.34 = 6.34, and instead I'm seeing a value of 11.11, which is 522/46.97 (the overall average across all the data).

Not sure if this calculation can me modified at all to account for the selections made in the slicers?

Help!

[v-huizhn-msft]

Hi @heidibb,

Have you resolved your issue? If you have, please marl the helpful reply as answer, or welcome to share your workaround. So that more people will benefit from here.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @heidibb,

Please add the ALLSELECTED in your formula, create "Index Hits" using the following formula and check if it works fine.

Index Hits='WeeklyAssessment Rollup'[Hits]/CALCULATE(AVERAGE('WeeklyAssessment Rollup'[Hits]),ALLSELECTED('WeeklyAssessment Rollup'))

If there is other issue, you'd better share your sample table or .pbix file for further analysis.

Best Regards,
Angelia