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:
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!
By my test, the two formulas you set is effective . The following is my test example.
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.
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.
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