Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 %?
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
could you pls provide some sample data?
Proud to be a Super User!
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |