Grishma Gajjar.

Data Validation (with Stakeholder Check)

To ensure data accuracy, I performed a manual validation step:

• - Connected with the stakeholder to gather key reference metrics (e.g., total revenue).
• - Cross-verified those numbers against the loaded dataset.
• - Created basic summary visuals (cards/charts) to confirm consistency before proceeding.

File Optimization (using DAX Studio)

To enhance model performance and reduce size, I conducted targeted file optimization:

• - Used DAX Studio to analyze table size and memory usage.
• - Removed derived columns that can be recalculated via database queries:
  • - gross_price
  • - pre_invoice_pct
  • - pre_invoice_amt
• - Eliminated calculated fields that replicate basic Excel logic and can be handled by Power BI visuals:
  • - total_cogs_amt
  • - gross_margin

Result: Reduced the Power BI file size by ~70 MB, improving load time and responsiveness.

No additional data transformation is required for the Sales view. We can directly build visuals using the existing model.

Essential Sales View Metrics

1. Performance Metrics
   • Net Sales ($) (NS $) – Total revenue from sales
   • Gross Margin ($ / %) (GM $ / GM %) – Profitability indicator
   • Customer/Store-level performance – Breakdown by customer with NS $, GM $, GM %
2. Customer Segmentation
   • Top Customers by Net Sales
   • Top Customers by GM %
   • Customer-wise Trend Comparison (vs LY or Target)
3. Product Segmentation
   • Product Category/Segment-wise NS $ and GM %
   • Best/Worst Performing Products
4. Unit Economics
   • Pre-invoice deductions
   • Post-invoice deductions
   • COGS (Cost of Goods Sold)
   • Visual Breakdown using donut/pie charts

The Sales view will include the following components:

• 1. Scatter Chart: Integrated with a slicer for target gap tolerance to analyze performance distribution.
• 2. Tooltip Creation: Designed custom tooltips to provide deeper insights on hover.

1. Scatter Chart with target gap tolerance

• Created a manual table using GENERATESERIES for user input through slicer:

                                  Target Gap Tolerance 2 = GENERATESERIES(0, 0.2, 0.01)
                                      

• Used this table as a slicer to let users select a GM% gap threshold.
• Created a variance calculation to measure deviation from the benchmark:

                                  GM % Variance = [GM BM %] - [GM %]
                                      

• Created a binary filter measure to include/exclude points from scatter chart:

  GM % Filter = 
      IF(
          [GM % Variance] >= 
          SELECTEDVALUE('Target Gap Tolerance 2'[Target Gap Tolerance]), 
          1, 
          0
      )
  

• Used GM % Filter in the visual-level filters of the scatter chart.

2. Tooltip Creation

Created new report, changed page settings and added necessary chart

for dynamic title:

Essential Marketing View Metrics

• Net Sales (NS $) per product
• Gross Margin (GM % and GM $)
• Net Profit (NP % and NP $)
• Monthly trend of NS $ and GM %
• Top-performing products
• Cost vs Margin (COGS donut chart)
• P&L breakdown (GM, OpEx, NP)