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

问题描述

这是我将获得的输出

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

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

此用例所需的最小度量定义如下所示.

按段总计 =如果(ISFILTERED(客户细分[细分])，计算([全部的]，筛选(顾客，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屋！