Get Percentage values using formula in YTD column

[romilv1]

Hi Guys,

Below is the matrix in Power BI. As you can see it brings Total Revenue & Gross Profit from database. We want to calculate Gross Profit %.

Formula is GP % (cell B4) = Total Revenue (B2) / Gross profit (B3)

The amount column is YTD column and the formula should preserve when user selects 12 months of data (amount in YTD)

hence for 12 months daata (row 8-11) also calculates GP% as given.

My Question is how to calcuate GP %?

[v-zhouwen-msft]

Hi @ryan_mayu ,thanks for the quick reply, I'll add further.

Hi @romilv1 ,

I've assumed some data.

Please follow these steps:
1. Use the following DAX expression to create a column

Amount(In YTD) = 
IF('Table'[Col-1] = "Gross Profit",[Amount],SUMX(FILTER('Table','Table'[Col-1] = "Total Revenue" && 'Table'[Year] = EARLIER('Table'[Year]) && 'Table'[Month] <= EARLIER('Table'[Month])),'Table'[Amount]))

2.Use the following DAX expression to create a column

GP % = IF([Col-1] = "Total Revenue",BLANK(), 
DIVIDE(MAXX(FILTER('Table','Table'[Year] = EARLIER('Table'[Year]) && 'Table'[Month] = EARLIER('Table'[Month]) && 'Table'[Col-1] = "Gross Profit"),'Table'[Amount]),
MAXX(FILTER('Table','Table'[Year] = EARLIER('Table'[Year]) && 'Table'[Month] = EARLIER('Table'[Month]) && 'Table'[Col-1] = "Total Revenue"),'Table'[Amount(In YTD)])
))

3.Final output

[ryan_mayu]

could you pls provide some sample data?