Forum Discussion
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:
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
- Greg_Deckler
Community Champion
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/339586.
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 - Ashish_Mathur
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.
