Forum Discussion

romilv1's avatar
romilv1
Icon for Helper I rankHelper I
2 years ago

Get Percentage values using formula in YTD column

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 %?

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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