cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sethwallin
Helper I
Helper I

Weighted average responding to slicers

Hello,

 

I'm sharing the pbix file because I've searched high and low for this, as well as asking here before, but haven't been able to figure it out.

 

Dynamic Weighted Average Test

 

I'm trying for a straightforward weighted average for our DSO. To do this, I'm using the following formulas:

 

1. SumProduct (Column) = 

[Amount * DSO]

 

2. Total of SumProduct (Measure) = 

CALCULATE(SUM(Sheet1[SumProduct]),ALLSELECTED(Sheet1))

 

3. Weight (Column) = 

[SumProduct] / [Total of SumProduct]

 

4. Weighted DSO (Column) = 

[Weight] * [DSO]

 

 

The problem: when a slicer is used, the [Total of SumProduct] changes, however the [Weight] remains the same. But that doesn't make sense, since the [Weight] is directly influenced by the [Total of SumProduct] value.

Capture4.PNG

Capture2.PNGCapture3.PNGI've read somewhere on here that Calculated Columns are only calculated one time at the initial creation, so they won't respond to slicers. But then what's the purpose of the ALLSELECTED filter I'm using in my [Total of SumProduct] formula if it doesn't change all the values affected when a selection is made?!?!

 

In theory, the [Weight] should always total to 1. For instance, the last screenshot should look more like this:

Capture.PNG

 

Thanks in advance and sorry for the long-windedness!!!

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Give this a try for your Weight measure.

Weight =
DIVIDE ( [SumProduct], CALCULATE ( [SumProduct], ALLSELECTED ( Sheet1[DSO] ) ) )

I put a copy of my testing file up for you to take a look at.  It also uses SUMX instead of a calculated column to get SumProduct.

https://www.dropbox.com/s/xmm25c0ahs58cxk/Weighted%20Avg%20DSO.pbix?dl=0 

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

Hey,

 

this articel provides an introduction into ALLSELECTED https://dax.guide/allselected/

 

As you can see it removes the implicit filter if you change the slicer for this reason all rows show the same value for Total of SumProduct (a measure), but this does not affect the calculation of the column Weight.

For this it's necessary that all calculations (except SumProduct) have to be measures as the are dependent on the slicer selection.

Unfortunately I can't use the filestore you choose, if you need more assistance please upload the pbix to onedrive or dropbox.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
jdbuchanan71
Super User
Super User

Give this a try for your Weight measure.

Weight =
DIVIDE ( [SumProduct], CALCULATE ( [SumProduct], ALLSELECTED ( Sheet1[DSO] ) ) )

I put a copy of my testing file up for you to take a look at.  It also uses SUMX instead of a calculated column to get SumProduct.

https://www.dropbox.com/s/xmm25c0ahs58cxk/Weighted%20Avg%20DSO.pbix?dl=0 

View solution in original post

Anonymous
Not applicable

@sethwallin  - Yes, it's true that Calculated Columns are calculated when the data is refreshed/processed. The value is stored in each row. Measures are calculated at query time. You want to use a measure for your weight, because your total needs to react to user interaction.

Hope this helps,

Nathan

 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors