cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heidibb Helper III
Helper III

divide row value by total of column including slicer selections

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)

 

Capture1.JPG

 

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).

 

Capture2.JPG

 

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

Help!

2 REPLIES 2
Microsoft v-huizhn-msft
Microsoft

Re: divide row value by total of column including slicer selections

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

Microsoft v-huizhn-msft
Microsoft

Re: divide row value by total of column including slicer selections

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors