Set expression within pivot table to calculate Margin % by month (Finance)

TomHollandKB — Tue, 24 Jan 2023 13:31:21 GMT

I have a pivot table with "Actuals" figures, the rows being different categories (e.g. Total Sales, Cost of Sales, Gross Margin), the columns being Period number.

Within the rows of the pivot, I also have GM1% and GM2% - GM1% being GM1/Total Sales, and so on for GM2%

I use an if expression in the table measure to determine what master measure to use (if row = GM1% then use GM1% master measure, else use "Total" master measure) - this is working fine, but...

My master measure for GM1%, I can't seem to figure out how to calculate the Margin % in the cell according to the corresponding period in the column.

I need to calculate both Total Sales and GM1 in master measures, then create another master measure to calculate GM1% based on the previously mentioned measures.

GM1 expression is: Sum({<[Total Class]={'GM1'} ,[Period reporting on] = {'=[Period reporting on]'}>} total Amount*-1)

Total sales expression is: Sum({<[Total Class]={'Total Sales'},[Period reporting on] = {'=[Period reporting on]'} >} total Amount*-1)

GM1 expression is: GM1/Total Sales

The problem i'm seeing is, each cell in the GM1% column is showing the same result - which is the GM1% for the whole year, not just for the corresponding month.

Can anyone see what is wrong with my set expression, or should I be using a different method entirely?

Please see below (bad and not very helpful) screenshot, I can't show or share any data unfortunately

Re: Set expression within pivot table to calculate Margin % by month (Finance)

TomHollandKB — Tue, 24 Jan 2023 15:18:56 GMT

I have found a workaround, although i'm sure there must be a more simple approach, I've used a pick expression to determine the GM% by month:

if(match([Total Class],'GM1%'),
num(
pick([Period reporting on],
[Actual GM1% P&L JAN],
[Actual GM1% P&L FEB],
[Actual GM1% P&L MAR],
[Actual GM1% P&L APR],
[Actual GM1% P&L MAY],
[Actual GM1% P&L JUN]
)
,'#0.00%'),
num(sum(Amount)*-1,vMoneyFormat)
)

Expression to calculate GM1 for January (named [Actual GM1 (P&L) JAN]):

Sum({<[Total Class]={'GM1'},[Period reporting on] = {'1'} >}total Amount*-1)

Expression to calculate Total sales for January (named [Actual Total Sales P&L JAN]):

Sum({<[Total Class]={'Total Sales'},[Period reporting on] = {'1'} >} total Amount*-1)

Expression to calculate GM1% for January:

[Actual GM1 (P&L) JAN]/[Actual Total Sales P&L JAN]

Obviously I still have the rest of the year to fill in, in the pick expression above, but it's working a treat, just a bit of a long-winded way of doing it, as i'll need to do the same for GM2 and GM2% measures.

However, fortunately I can re-use the "Total Sales" measures to calculate GM2%.

topic Set expression within pivot table to calculate Margin % by month (Finance) in App Development

Set expression within pivot table to calculate Margin % by month (Finance)

Re: Set expression within pivot table to calculate Margin % by month (Finance)