SUM with calculated filed - DAX query

Question

I am trying to calculate sum from a calculated field

sum(Productions201801.Piece * Item.Factor) (sql code)

This is the DaxQuery for calculate simple the Sum. All table are in directly ralation with the Fact Table (Productions201801)

EVALUATE CALCULATETABLE (
 SUMMARIZE (
     Productions201801,
     Times[TimeID],
     Color[Code],
     st[Name],
     OOR[OOCode],
     "Qty", SUM ( Productions201801[Pieces] )
 ),
 st[Name] = "Bor1",
 OOR[OOcode] = "OO-1" ) ORDER BY
 [TimeID],
 [Code],

Now I want to modify the dax query for SUM value this

SUM ( Productions201801[Pieces] * Item[Factor])

but Item table is not directly related with table Productions201801, is realted with table OOR by Id filed.

Someone can help me to find the right solution?

Add Realtion image enter image description here

Could you include a screenshot of the relationships of the tables you mention? — mendosi, Feb 13 '18 at 15:20

score 0 · Answer 1 · answered Feb 13 '18 at 22:33

0

I can't test this since I don't have the tables your working with, but I believe you should be able to replace SUM(Productions201801[Pieces]) with something like the following:

SUMX(Productions201801, Productions201801[Pieces] * Item[Factor])

answered Feb 13 '18 at 22:33

Alexis Olson

38,724
7
42
64

I Alexis, thank you for your suggestion but sql engine give me error becouse there is not direct relationship between table Productions201801 and Item table. – Andrea Da Como Feb 14 '18 at 07:31

score -1 · Answer 2 · answered Feb 13 '18 at 13:42

-1

You can do a select in a select...

SELECT sum(mult) FROM (SELECT Productions201801.Pieces, Item.Factor, 
Productions201801.Pieces*Item.Factor as mult
FROM Productions201801
INNER JOIN ITEM ON Productions201801.id=Item.id);

answered Feb 13 '18 at 13:42

C.Lechner

19
5

I want the DAX query, with the same result set: Times[TimeID], Color[Code], st[Name], OOR[OOCode], "Qty" – Andrea Da Como Feb 13 '18 at 13:48

SUM with calculated filed - DAX query

2 Answers2