cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

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

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 ?

1 ACCEPTED SOLUTION
Microsoft Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

2 REPLIES 2
Microsoft Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Post Patron

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors