Total Cost of all products by month excluding duplicates as a calculated column to put in a matrix

[RogerSteinberg]

Hi,

I have 5 columns: Date, Cost, Month, Revenue, Product

• Date : The initial investment month
• Cost: Initial investment cost
• Month: Future months generating revenue. For example a '1' means one month after 'Date'
• Revenue: Revenue generated after the initial investment cost
• Product: Variety of products having their cost and revenue

My dataset looks like this :

Date	Cost	Month	Revenue	Product
2018-01	10	1	20	A
2018-01	10	2	30	A
2018-01	25	1	50	B
2018-01	25	2	100	B
2018-02	5	1	15	A
2018-02	5	2	25	A
2018-02	30	1	30	B
2018-02	30	2	60	B

I would like to create a calculated column that sums the cost for all products by Month.

For example in 2018-01, the cost should be 35 and 35 as well for 2018-02. 

My final out should look like this: 

MATRIX

Date	Cost	1	2
2018-01	35	70	130
2018-02	35	45	85

Making a measure for this is simple but since we can't put a measure in the "Rows" of a matrix I need to make this as a calculated column

So I am thinking that in order to achieve this I need in my matrix fields:
Rows: Date and Cost (new column)

Columns: Month

Values: Revenue (Summed aggregation

How can I achieve this ?

[mahoneypat · Accepted solution]

Please try this expression in your column.  I called your example table "Cost", so replace that with the actual table name.

Cost All =
CALCULATE ( SUM ( Cost[Cost] ), ALLEXCEPT ( Cost, Cost[Date], Cost[Month] ) )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[RogerSteinberg]

Using the formula works on my dummy pbi.

However, for some reason , it does not work in my actual report. I know im not giving you much info here but could there be a reason why the aggregations doesn't work? It giving me different Cost for Each Month. It should be the same regardless of the Month values. It should be aggregated on the basis of the Date column