cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sethwallin Regular Visitor
Regular Visitor

Divide a value by the filtered total of a column

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.

Capture.PNG

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
Community Support Team
Community Support Team

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

Hi @sethwallin 

 

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.

Capture.PNG

  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 .

image005.pngimage004.png

 

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
Regular Visitor

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

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.

Capture.PNG
 
2. However, when shift the slicer, the table isn't changing the calculations/values, only what it's displaying.
Capture2.PNGCapture3.PNG
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
Regular Visitor

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

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.

Capture.PNG

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

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 230 members 2,775 guests
Please welcome our newest community members: