如何对月份进行客户细分并查看已过滤的客户编号？ [英] How can I do a customer segmentation for month and see the customer number filtered?

问题描述

这是我将获得的输出

模型图将如下所示。 CustomerSegments表与其他表没有关系。我介绍了另一个附加的客户维表，其中包含唯一的客户。这有助于使度量定义保持简单并提高性能。

由于细分表是断开连接的表，因此应用于细分的切片器将不会自动传播到总计度量。您需要修改度量定义，以便它动态评估客户细分，并显示属于当前所选细分的客户的总数。

 按细分总计= 
 IF（
 ISFILTERED（CustomerSegments [段]），
 CALCULATE（
 [总计]，
 FILTER（
客户，
 CALCULATE（[最近3个月总计]）= SELECTEDVALUE（CustomerSegments [TotalLast3Months] ）
）
），
 [总计] 
）
  

使用细分维度和上面定义的度量，您将获得所需的输出。

This is the output I would obtain https://i.stack.imgur.com/er8Du.png This is the sample pbix I have done https://mega.nz/#!lg9GASzR!D861nKR8cFGlm4eNyEa2BTep3ku_M5JjktvITAePmQ4

I would obtain the number of customers for each month with the label defined in the measure

m-mark = 

IF([sumlast3months]=3;"Gold";
IF([sumlast3months]=2;"Silver";
IF([sumlast3months]=1;"Bronze"; 

;BLANK())))

How can I find new customers in each month? I have thought to find them with a groupby for customerid with the min data of buying but this way I find only them that are new in this month. Do you have an idea?

解决方案

You can apply Dynamic Segmentation pattern introduced by Marco Russo.

Overall direction to solve this problem is to create a disconnected table for Segmentation, and handle the slicer on Segmentation in your Total (or any) measure.

You need an additional table for customer segmentation to slice by each label such as "Gold" or "Silver". Segmentation table has a column for the label and the criteria for dynamic segmentation (in this case TotalLast3Months). Criteria is only to be used in measures, so it is recommended to make it hidden.

The model diagram will be looking like this. CustomerSegments table has no relationship with other tables. I have introduced one more additinal Customers dimension table, which holds the unique Customers. This is helpful to keep the measure definition simple, and to improve the performance.

As Segmentation table is a disconnected table, the slicers applied on Segmentation will not be propagated to the Total measure automatically. You need to modify the measure definition so that it evaluates the customer segments dynamically, and shows the Total number for Customers who belongs to currently selected Segment.

Minimal measure definition required for this use case would look like this.

Total by Segment = 
IF(
    ISFILTERED(CustomerSegments[Segment]),
    CALCULATE(
        [Total],
        FILTER(
            Customers,
            CALCULATE([Total Last 3 Months]) = SELECTEDVALUE(CustomerSegments[TotalLast3Months])
        )
    ),
    [Total]
)

Using the Segmentation dimension and the measure defined above, you will get the desired output.

这篇关于如何对月份进行客户细分并查看已过滤的客户编号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！