Expression Issue

[rittermd]

Hopefully I can explain what is happening clearly here.

I have a straight table with about a dozen measures.

I change the Dimension of this table based on a list box choice.  That works fine.

The results that I get are correct for 10 of the 12 Dimensions.  Two don't work the same and I can't figure out what is different.

If I use this expression I get the correct grand total but the rows for the 2 Dimensions in question do not calculate correctly and many of them just display a - in a grey box.

Avg(Aggr(Sum(DISTINCT LOS),CaseNumber)).  But only for the two dimensions.

If I change to this expression then I get results in every row (the - grey box goes away) but the results are not correct.

Avg(Aggr(NODISTINCT Sum(DISTINCT LOS),CaseNumber)) for these two dimensions. 

I had actually created this expression so that it would change the expression being used based on the Dimension picked.

If (Match(Display, 'By FDScale','By MeasureDesc'),Avg(Aggr(NODISTINCT Sum(DISTINCT LOS),CaseNumber)),Avg(Aggr(Sum(DISTINCT LOS),CaseNumber)))

I am not sure what else to look at.

[sunny_talwar · Accepted solution]

Give this a shot:

Avg(Aggr(Sum(DISTINCT LOS),CaseNumber, $(='[' & only(Dim1) & ']')))

[rittermd]

This seems to have fixed the calculations for each row.  However, the grand total is off compared to the other Dimensions. 

So I will dig into this further and see what the issue might be.  Or it could be correct. 

I am attempting to clone an existing report that is very flawed.  So I don't necessarily have correct numbers to compare to. 

Thank you so much for your help.

[sunny_talwar]

What is the expectations with the totals? Sum of rows? Avg of rows? something else?

Also is this in a straight or pivot table? You can always use Total mode for straight table.

[rittermd]

It is a straight table.  I would have expected the grand total to be the same for every dimension.  They are for most of the measures.

But again at this point I am not 100% sure without digging a little deeper.

In this case it should be the total average LOS for all cases.

[sunny_talwar]

If you grant total is an average, then an average based on one dimension most likely won't equal average based on another dimension. Sum's are a different story (they should match up).