Cumulative Spend and Top N Percentage of Customers Tutorial

Overview

• Objective: Learn how to calculate cumulative spend and top N percentage of customers using a dynamic slider in Power BI.
• Data Tables:
  • Top Customers
  • Bottom Customers
  • All Customers

Dynamic Customer Identification

• Use a slider to dynamically adjust the percentage of total sales (e.g., move slider to 90% to see customers contributing to 90% of sales).

Creating Measures

Total Sales Measure

• Formula:

  total_sales = CALCULATE(SUM(SalesAmount))
  

• Represents the total sales amount.

Cumulative Spend Measure

• Formula:

  cumulative_spend = CALCULATE(
      total_sales,
      WINDOW(
          FROM_VALUE = 0,
          ABSOLUTE,
          TO_VALUE = 2,
          RELATIVE = 0,
          ADDCOLUMNS(
              ALLSELECTED(CustomerName),
              "Cast",
              total_sales,
              ORDER BY Cost DESC
          )
      )
  )
  

• Calculates cumulative sales value based on rows in the table.

Cumulative Spend Percentage Measure

• Creating Variable:
• Formula:

  cumulative_spend_percentage = 
  VAR all_sales = CALCULATE(SUM(total_sales), ALL(GrocerySaleTable))
  RETURN
      DIVIDE(cumulative_spend, all_sales)
  

• Shows the percentage contribution of each customer to total sales.

Filtering Customers

• Purpose: Identify customers contributing to a specific percentage of total sales.
• Filter Creation:

  filter = 
  IF(cumulative_spend_percentage <= 0.3, TRUE, FALSE)
  

• Apply filter to display customers whose cumulative spend percentage is below or equal to 30%.

Making Filters Dynamic

• Creating a Dynamic Slider:
  • Navigate to the Modeling tab.
  • Create a new parameter for numerical range:
    • Name: Top
    • Range: 0 to 1 (increment by 0.1)
  • Link slider value to the filter measure:
    • Update filter formula to use the slider value instead of hardcoded 0.3.

Displaying Bottom Customers

• Change filter to show customers contributing to the bottom percentage of sales by switching TRUE to FALSE in the filter equation.

Conclusion

• The tutorial explained how to dynamically calculate and display cumulative spending and identify top customers using advanced measures and sliders in Power BI.
• Call to Action:
  • Consider subscribing for more tutorials.