How do I prevent summing on a column in a Table where the rows are aggregations?

[Anonymous]

Hi all,

Let's say you have a Sale query and a StockCard query.  They are linked by StockID.  In the Stock table is a StockOnHand column.

How do you create a table visualisation that sums Sale values (saleValue, GST etc) and also shows values from the Stock query that are NOT aggregated.

eg.

2 sales for $10 each for the same Stock record.  Stock has StockOnHand of 100.

My issue is that the table shows...

Product    Total    StockOnHand

Coke         $20       200

Basically it is adding the stockOnHand twice because there are 2 sales.

This is killing me.

Free beer in Sydney for the winner.

Nick

[himanshu56]

Hi Nick,

Please try the below DAX query.

stockOnHand_Distinct =
SUMX (
    DISTINCT ( TableName[ProductName] ),
    CALCULATE ( DISTINCT ( TableName[stockOnHand] ) )
)

Thanks,

Himanshu

[Anonymous]

Hi,

Thanks for helping me.  Should that DAX be a measure on the Sales table or the Stock table?  It's giving me an error.

Nick

[Anonymous]

Error Message:
MdxScript(Model) (11, 91) Calculation error in measure 'Sales'[stockOnHand_Distinct]: A table of multiple values was supplied where a single value was expected.

[Anonymous]

Seems to work at the lowest level of my heirachy.  But when I Drill-Up, it throws that error.

[Anonymous]

I should add that the table has a 3 level heirachy.  Product, Sub Department and Department.

[himanshu56]

Not sure how to handle heirarchy in this particular situation.

May be you need to use Filter function.