Calculating numerical distribution if customer listed all 3 products

To calculate the "Distribution (Number of Listed Customers)" for customers who have positive sales for all three products (A, B, and C) within a given period in Power BI, you can use the following approach:

1. Create a calculated column to mark each customer's positive sales record for each month. This will help identify customers who have bought all three products with positive sales values.

2. Create a measure to calculate the total sales value and count of qualifying customers for each month and quarter.

Here’s a step-by-step approach:

Step 1: Create a Calculated Column for Positive Sales Status

In Power BI, add a calculated column to your table to identify if a customer has positive sales for each product in a given month:

//DAX
PositiveSalesStatus =
IF([SALES IN EUR] > 0, 1, 0)

Step 2: Calculate the Sales Value and Distribution per Month

For the Monthly Sales Value and Monthly Distribution (Count of Listed Customers), you can create measures:

1. Sales Value per Month:

//DAX
MonthlySalesValue =
CALCULATE(SUM(Table[SALES IN EUR]),
ALLEXCEPT(Table, Table[MONTH]))

2. Monthly Distribution (Customers who purchased all 3 products):

//DAX
MonthlyDistribution =
CALCULATE(
DISTINCTCOUNT(Table[CUSTOMER NAME]),
FILTER(
VALUES(Table[CUSTOMER NAME]),
CALCULATE(COUNTROWS(FILTER(Table, Table[PositiveSalesStatus] = 1 && Table[PRODUCT NAME] = "PRODUCTA")) > 0) &&
CALCULATE(COUNTROWS(FILTER(Table, Table[PositiveSalesStatus] = 1 && Table[PRODUCT NAME] = "PRODUCT B")) > 0) &&
CALCULATE(COUNTROWS(FILTER(Table, Table[PositiveSalesStatus] = 1 && Table[PRODUCT NAME] = "PRODUCT C")) > 0)
)
)

This measure will count only those customers who have purchased all three products (A, B, and C) with positive sales within the selected month.

Step 3: Calculate the Quarterly Totals

For the Quarterly Total Sales Value and Quarterly Distribution:

1. Quarterly Sales Value:

//DAX
QuarterlySalesValue =
CALCULATE(SUM(Table[SALES IN EUR]),
DATESQTD(Table[MONTH]))

2. Quarterly Distribution:

//DAX
QuarterlyDistribution =
CALCULATE(
DISTINCTCOUNT(Table[CUSTOMER NAME]),
FILTER(
VALUES(Table[CUSTOMER NAME]),
CALCULATE(COUNTROWS(FILTER(Table, Table[PositiveSalesStatus] = 1 && Table[PRODUCT NAME] = "PRODUCTA")) > 0) &&
CALCULATE(COUNTROWS(FILTER(Table, Table[PositiveSalesStatus] = 1 && Table[PRODUCT NAME] = "PRODUCT B")) > 0) &&
CALCULATE(COUNTROWS(FILTER(Table, Table[PositiveSalesStatus] = 1 && Table[PRODUCT NAME] = "PRODUCT C")) > 0)
),
DATESQTD(Table[MONTH])
)

This will count customers who have positive sales for all three products across the entire quarter, accounting for customers who may have purchased the products in different months within the quarter.

These measures should provide the sales value and distribution as outlined in your example for each month and the overall quarter.

I hope the answers was helpful. If it was, please leave a kudos or mark it as solution.