cancel
Showing results for
Did you mean:  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.   I'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: Thanks in advance and sorry for the long-windedness!!!

1 ACCEPTED SOLUTION  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

3 REPLIES 3  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  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 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  