cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BerrySundae
New Member

Making a Calculated Column like in a Pivot Table in PowerBI Matrix

Hello, I am simply trying to make a calculated column in a PowerBI Matrix like this one in Excel:

 

https://imgur.com/a/ouvab8W

 

As you can in the image link, column D is a calculated column that simply divides the cell in column C by the cell in column B.

 

I am trying to duplicate this in a PowerBI matrix. I cannot use normal aggregation methods because they end up weighted incorrectly. For example, there are a lot of products with low profiability, but they do not have many sales. Normal methods incorrectly average them with everything else instead of weighting the average profit by the amount of sales.

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

Write these measures

Net sales = sum(Data[Net sales $])

Gross profit = sum(Data[Gross profit $])

Gross profit (%) = divide([Net sales],[Gross profit])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

@BerrySundae You will need something to define "previous" and then use something like this: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors