PowerPivot 'Infinity' Issue

[PerryN1]

Hi,
i currently have the following table..

Day Date MD Mode Person Volume VolumeMDAvg VolumeZscore


VolumeMDAvg is the average volume filtered by person and MD.

Im currently getting an infinity reading in the zscore column, however this only appears when the MD is -2 (range between -1 and -4)

Basically my issue appears to be in the standard deviation used for the zscore column. If I create another column and use the stdev part of the formula for the zscore it reutrns the number

8.9742083910604E-320

<tbody>
</tbody>

When calculated properly the stdev should be 1.82.

Obviously I can provide more details but anyone any initial ideas?

Thanks

 

[PerryN1]

<table verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"=""><tbody>[TR]
[TD]To provide some more info using just the stdev as this is where the issue lies...

This is the table in powerpivot filtered to the MD and Person the issue is happening with. Blank spaces are where there is no data. In the table there are about 25 persons 5 different MD values and days 1 through to 186. I find it very bizarre as there is very similar data for other persons however the stdev is fine. In the example below, the formula I'm currently using for Volume_Stdev is

=calculate(STDEV.S(Table[Volume]), FILTER(table, table[Person]=EARLIER(table[Person]) && Table[MD]=EARLIER(Table[MD])))

The value I get with the data below is <table><tbody><tr>[TD]4.45449586290468E-320 however should be about 85.

Maybe it is also worth noting that this model was fine until having to download and use Excel 2016.[/TD]
</tr></tbody></table>
Day | MD | Person | Volume | Volume_Stdev
<table width="px"""><tbody><tr>[TD]185 -2 Person1 500[/TD]
</tr><tr>[TD]178 -2 Person1 600[/TD]
</tr><tr>[TD]164 -2 Person1 550[/TD]
</tr><tr>[TD]157 -2 Person1 550[/TD]
</tr><tr>[TD]143 -2 Person1 500[/TD]
</tr><tr>[TD]136 -2 Person1 600[/TD]
</tr><tr>[TD]129 -2 Person1 700[/TD]
</tr><tr>[TD]122 -2 Person1 500[/TD]
</tr><tr>[TD]107 -2 Person1 600[/TD]
</tr><tr>[TD]100 -2 Person1[/TD]
</tr><tr>[TD]93 -2 Person1[/TD]
</tr><tr>[TD]86 -2 Person1[/TD]
</tr><tr>[TD]79 -2 Person1[/TD]
</tr><tr>[TD]79 -2 Person1[/TD]
</tr><tr>[TD]72 -2 Person1[/TD]
</tr><tr>[TD]65 -2 Person1[/TD]
</tr><tr>[TD]37 -2 Person1 750[/TD]
</tr></tbody></table>
[/TD]
[/TR]
</tbody></table>Thanks for any help!

 

[PerryN1]

...Minus all the TD and TRs, not sure where they appeared from!

 

[Ozeroth]

This should work:

=
CALCULATE (
    STDEV.S ( Table[Volume] ),
    ALLEXCEPT ( Table, Table[Person], Table[MD] )
)

Using ALLEXCEPT and specifying the columns that you want to remain in the filter context ensures that any unwanted columns (added by the context transition) are removed from the filter context.

In your case, I think you were getting the STDEV of a single value for every row.

 

[Ozeroth]

Actually, I think your formula should work, but for some reason doesn't in Excel 2010 & Excel 2016.

It should work because in the 2nd argument of CALCULATE you are filtering all columns of 'Table' in an unfiltered filter context, so the resulting filtered 'Table' will over-ride any filter context produced by the context transition of the row context, and produce 'Table' filtered down to current Person & MD. This should produce the same result as my ALLEXCEPT version.

I've tested it out in every version of Excel I have & Power BI Desktop.

Version	FILTER formula	ALLEXCEPT formula
Excel 2010	Blank	Correct
Excel 2013	Correct	Correct
Excel 2016	Very small number	Correct
Power BI Desktop (2.29)	Correct	Correct

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Does anyone out there know why the behaviour would differ between Excel versions?

Just to recap, we are adding a calculated column to the table in PerryN1's original post, using two possible formulas.
The FILTER version of the formula produces apparently wrong results in Excel 2010 & 2016.

FILTER formula:

=
CALCULATE (
    STDEV.S ( 'Table'[Volume] ),
    FILTER (
        'Table',
        'Table'[Person] = EARLIER ( 'Table'[Person] )
            && 'Table'[MD] = EARLIER ( 'Table'[MD] )
    )
)

ALLEXCEPT formula:

=
CALCULATE (
    STDEV.S ( Table[Volume] ),
    ALLEXCEPT ( Table, Table[Person], Table[MD] )
)

 

[PerryN1]

Thanks a lot for your help Ozeroth, the allexcept seems to work fine.

That was going to be my other question, why would it work in 2013 but not 2016!? Hopefully won't encounter too many other issues with the swap!