Weighted average responding to slicers

[Anonymous]

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!!!

[TomMartens]

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

[jdbuchanan71 · Accepted solution]

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]

@Anonymous  - 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