Divide a value by the filtered total of a column

[Anonymous]

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!

[v-xuding-msft]

Hi @Anonymous 

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

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

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

[Anonymous]

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.

[Anonymous]

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