Calculations

[MNGoodyear]

HI,

I am sure this will be simple but very annoying.

I have cretaed a very simple example to demonstrate my frustration/inability

In the below table I have created the margin column and the total margin column using DAX formula

if you take total margin i have created a formula as follows:

Total Margin =
(SUM('Report 1'[Price]) - SUM('Report 1'[Cost])) * SUM('Report 1'[Qty])

This works when dispalying the data in a table , but the totals dont work  and similarily it wouldn't work if i wanted to display the value in a "Card" , as the context of the "product" has been lost.

In the example the total for "Total Margin" should be 2480, not 9900

I apprciate this is because i am using "Sum" in the calulation but cannot see any other way.

In other tools you can apply context by saying somthing like:

 (SUM('Report 1'[Price]) - SUM('Report 1'[Cost])) * SUM('Report 1'[Qty]) in ('Report 1'[Product])

Product	price	Cost	margin	qty	Total Margin
a	100	80	20	5	100
b	50	40	10	10	100
c	30	28	2	15	30
d	200	150	50	20	1000
e	150	100	50	25	1250
Totals	530	398	132	75	9900

Is there a simple way to achieve this

Thanks

[Zubair_Muhammad · Accepted solution]

@MNGoodyear

Use this MEASURE instead.

Hopefully this will work for both table and card visual

Total MArgin = SUMX('Report 1',([price]-[Cost])*[qty])

[MNGoodyear]

Thanks very much