Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Hi Nick,
Please try the below DAX query.
stockOnHand_Distinct =
SUMX (
DISTINCT ( TableName[ProductName] ),
CALCULATE ( DISTINCT ( TableName[stockOnHand] ) )
)
Thanks,
Himanshu
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
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.
Seems to work at the lowest level of my heirachy. But when I Drill-Up, it throws that error.
I should add that the table has a 3 level heirachy. Product, Sub Department and Department.
Not sure how to handle heirarchy in this particular situation.
May be you need to use Filter function.