- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Divide a value by the filtered total of a column

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

sethwallin

Regular Visitor

Divide a value by the filtered total of a column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-05-2019
07:31 AM

Apologies on the similar nature to some of the other questions.

I'm attempting to build a weighted average for the DSO of invoices, but I am having trouble getting it to respond to slicer selections. Below is some dummy data reflecting what I want to calculate *after* the slicer selection. The entire data set is over a million lines long.

In Power BI, I've used the following formula to get the total of column 'C' and verified that it works with slicer selections:

CALCULATE(SUM(Table1[DSObyAmount]),ALLSELECTED(Table1))

I then try to use that to get the weight (column 'D'):

Table1[DSObyAmount] / CALCULATE(SUM(Table1[DSObyAmount]),ALLSELECTED(Table1))

However, instead of only using the filtered values from the slicer selection, it SUMs the *entire* column of Table1[DSObyAmount] and uses that as the denominator, which is over a million lines long. What am I doing wrong here? Since I'm using ALLSELECTED, shouldn't it only use the total of those 6 lines (43,790,307.50)?

Thanks in advance for the help!

3 REPLIES 3

v-xuding-msft

Established Member

Re: Divide a value by the filtered total of a column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-06-2019
02:48 AM

Hi @sethwallin

By my test, the two formulas you set is effective . The following is my test example.

- I add three DSO, 300,301 and 302 to the table. The column C is the sum of column DSO * Amount. The Column D is the weight . And the two column both use the formula you set.

- When I slice the table from 300 to 303, the table will be change automatically. The column C is the sum of the DSO*Amount which is filtered by the slicer, so the ALLSELECTED function should work. The same as slice from 316 to 323 .

Please check the problem of other aspects. If you still need help, please share your desired output for this scenario so that we could help further on it.

Best Regards,

Xue Ding

If this post **helps**, then please consider ** Accept it as the solution** to help the other members find it more quickly.

sethwallin

Regular Visitor

Re: Divide a value by the filtered total of a column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2019
12:50 PM

Hi,

I know this has been closed for a while, but I was pulled away to another project, so this was put on hold.

I tried to replicate your example exactly, just to be sure I was doing it right, and I'm still having the same issue, except now the Total column doesn't even change, which is frustrating.

1. This is just to show I'm still using the same "Total" formula with the ALLSELECTED filter.

2. However, when shift the slicer, the table isn't changing the calculations/values, only what it's displaying.

So the slicer is working on the table, but it isn't changing the underlying data is any way. I feel like there must be some setting that is not allowing this to work.

sethwallin

Regular Visitor

Re: Divide a value by the filtered total of a column

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-01-2019
01:45 PM

Just to clarify, I know my "Total" field wasn't changing in the previous screenshots, which would make it look like that was the issue, but I was simply using a column instead of measure. The below screenshot shows the Total changing with the slicer.

However, even using this measure, the Weight column is still dividing the "DSO * Amount" by 55,825,943, instead of by the new "Total" of 12,035,635.50