Dataset: DD-RET-001
Question: DD-RET-001-Q022
Replies
For this question, my answer is not matching with the answer mentioned in the solution. My answer is Feb 2026 and %age is 30.26. In the suggested solution, the answer is June 2026 and percentage is 32.0%.
I followed following steps.
1-First calculated gross margin of each sales transaction (Net sales-COG)/Net sales.
2-Then I converted it into %age and then applied pivot.
Kindly guide where I'm making mistake
I followed following steps.
1-First calculated gross margin of each sales transaction (Net sales-COG)/Net sales.
2-Then I converted it into %age and then applied pivot.
Kindly guide where I'm making mistake
Hi, your margin formula is right. The issue is in the pivot. You worked out a % for each transaction and the pivot averaged them, so a small sale counted as much as a big one.
Monthly margin should be Total Gross Margin ÷ Total Net Sales for the month.
To fix it in Excel: put Sum of GrossMarginSAR and Sum of NetSalesSAR in the pivot, then add a calculated field = GrossMarginSAR / NetSalesSAR. June 2026 should come out on top at about 32.0%.
In Power BI you write this logic once as a measure, and it then works at every level (day, month, store) with no helper columns:
Gross Margin % = DIVIDE( SUM(FactDailySales[GrossMarginSAR]), SUM(FactDailySales[NetSalesSAR]) )
Excel works for now because the datasets are small. At L3 and L4, though, the data volume and logic go beyond what Excel can handle. Start building your Power BI and Python skills now.
Monthly margin should be Total Gross Margin ÷ Total Net Sales for the month.
To fix it in Excel: put Sum of GrossMarginSAR and Sum of NetSalesSAR in the pivot, then add a calculated field = GrossMarginSAR / NetSalesSAR. June 2026 should come out on top at about 32.0%.
In Power BI you write this logic once as a measure, and it then works at every level (day, month, store) with no helper columns:
Gross Margin % = DIVIDE( SUM(FactDailySales[GrossMarginSAR]), SUM(FactDailySales[NetSalesSAR]) )
Excel works for now because the datasets are small. At L3 and L4, though, the data volume and logic go beyond what Excel can handle. Start building your Power BI and Python skills now.
Sign in to reply to this thread
Reading is open to everyone. To reply, compare your result or post your approach you need a free findi360 account and a public username — your email is never shown.