User Tools

Site Tools


bicn01:f5009

PowerPivot: MarketShare

In the PowerPivot ribbon, create new measures:

  • Table name: F_Monthly_Sales
  • Measure name: Margin
  • Formula: =[Sum of Gross_Profit] / [Sum of Revenue]
  • Formatting Category: Number, 2 Decimal places
  • Table name: F_Monthly_Sales
  • Measure name: Profitability
  • Formula: =[CompanyProfit]/[Sum of Revenue]
  • Formatting Category: Number, 2 Decimal places
  • Table name: F_Monthly_Sales
  • Measure name: MarketSharePct
  • Formula: =[Sum of Sales_Amount]/12000
  • Formatting Category: Number, 2 Decimal places
For future improvement, you may read the market share from the dimension table.

=CALCULATE([MarketBikes];ALL(D_Store); D_Store[Store_Name] = “Ludwigshafen”)

Create a PowerPivot query

Rows Values
Calender_Month_ISO Sum of Revenue
Sum of Gross_Profit
Margin, format as %
Sum of Cost_Value
CompanyProfit
Profitability, format as %
Sum of Sales_Amount
MarketSharePct, format as %

Your query should look like this:

Note your market share.

bicn01/f5009.txt · Last modified: 2018/12/04 08:39 (external edit)