Skip to main content
cancel
Showing results for 
Search instead 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

Reply
RogerSteinberg
Post Patron
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  CostMonthRevenueProduct
2018-01 10120A
2018-01  10230A
2018-01  25150B
2018-01  252100B
2018-02  5115A
2018-02  5225A
2018-02  30130B
2018-02  30260B

 

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  Cost12
2018-01  3570130
2018-02  354585

 

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
mahoneypat
Microsoft Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Microsoft Employee
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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