DAX filter problems

[jmcph]

Hi, I hope you can help me with my little problem.

I have this table. 

Book                 Reference #                 Code                   Date                   Amt
Eco                       Ref01                         101                   1/1/2020           1,000

Pro                       Ref01                         101                  2/5/2020           1,000

Eco                       Ref02                         101                  2/1/2020           2,000

Eco                       Ref02                         101                   2/3/2020           2,000

My measure are as follows : 

EcoAmt = Sumx ( Table , if( Book = "Eco", Table[Amt] , 0 )

ProAmt = Sumx ( Table , if( Book = "Pro", Table[Amt] , 0 )

What i expected my visual table would look like

Ref #         Code       EcoAmt           ProAmt

Ref101        101          1,000              1,000

However, this doesnt seem to be the case. Any ideas on how to fix this? 

Any inputs would be greatly appreciated. 

Thank you! 

[amitchandak · Accepted solution]

@jmcph , This should work. what is the wrong you are getting? You should also get a row for Ref102.

try measure like

Sumx (filter(Table,[Book] = "Eco"), Table[Amt])
Sumx (filter(Table,[Book] = "Pro"), Table[Amt])

[v-alq-msft · Accepted solution]

Hi, @jmcph 

You may create measures as below. The pbix file is attached in the end.

EcoAmt = 
CALCULATE(
    SUM('Table'[Amt]),
    FILTER(
        'Table',
        [Book]="Eco"
    )
) 

ProAmt = 
CALCULATE(
    SUM('Table'[Amt]),
    FILTER(
        'Table',
        [Book]="Pro"
    )
) 

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak · Accepted solution]

@jmcph , This should work. what is the wrong you are getting? You should also get a row for Ref102.

try measure like

Sumx (filter(Table,[Book] = "Eco"), Table[Amt])
Sumx (filter(Table,[Book] = "Pro"), Table[Amt])