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:
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)?
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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