Why is data doubled up here?

[sarahallen1]

I have reduced data down to one example selection state which gives this issue.  So no selections are now needed to replicate this.

I have an expression (for now just in a text box - stripped it right back to what seems to be the root problem):

• count(Value*[Weight in Fund])

Which gives a count of 2.  I can't see why this is - can't see any multiple rows of non-island data.

Individually, the components of the product are:

• count(Value)
• count([Weight in Fund])

which both give a count of 1, as expected.

NB I'm actually wanting to do a sum, but I'm just troubleshooting with a count for now.

Reduced QVW attached.

Any help is much appreciated - this is bubbling up to a weighted average style calc in a pivot table and REALLY confusing users when they cross check with Excel.  I have no idea why it is doubled up.

Thank you!!

[vishsaggi]

May be try this?

= Count(Value)* Count([Weight in Fund])

OR

= Count(DISTINCT (Value * [Weight in Fund]))

[vinieme12 · Accepted solution]

it is because , you Main table (CompanyAdditional) thru which  Value is Associated to Weight in Funds has 2 rows of Data for the same value

Try

=count(Distinct Value*[Weight in Fund])

[sunny_talwar]

May be to Sum, you need this:

=Sum(Aggr(Value*[Weight in Fund], File))

[sarahallen1]

Thank you vinieme12‌ , vishsaggi‌, stalwar1‌ .

Vineeth -

I had not spotted that doubled data in CompanyAdditional.  This seems to be the root problem (and explains why, in the non-reduced version of my QVW, I have the problem for some selections and not others).

Vishwarath -

Unfortunately I actually need to do a sum product in a pivot table (expandable to the individual components too).  This count is a simplification of where the problem occurs.

sum({$<Value=-{"",'$(null())'}>}Value*[Weight in Fund])

        /

        sum({$<Value=-{"",'$(null())'}>}[Weight in Fund])

so I can't use count(distinct...) either, as some of the values might be identical for different rows

Sunny -

I will try using aggr in my function like you suggest, over File - thanks

Will update you all once tested!

I will also look upstream in the data processing to find out why I've got duplicates in CompanyAdditional. 

[sarahallen1]

Found the reason for the duplicates - a dodgy left join.  Now resolved. 

Thanks all!

[vinieme12]

Cheers