在 Power BI 中计算累积值 [英] Calculating the cumulative values in Power BI

问题描述

我有两张桌子:

• 用户表(包含:用户注册数据.列:user_id、create_date)
• 客户订单表(包含:订单历史记录.列:user_id、order_date、order_id)

*用户和客户不一样.当用户注册他的第一个订单时，他就成为了客户.

*user and customer aren't the same. when a user registers his first order, he becomes a customer.

对于每年的每个月，我想要不同用户的累计数量和不同客户的累计数量，因为最后，我想计算不同客户的累计数量的比率 每个月不同用户的累计计数.

For each month of each year, I want the accumulative count of distinct users and the accumulative count of the distinct customers because at last, I want to calculate the ratio of the accumulative count of the distinct customers to the accumulative count of the distinct users for each month.

我不知道如何使用 DAX 计算累积值和我所说的比率.

I don't know how can I calculate the accumulative values and the Ratio that I said, using DAX.

请注意，如果客户在一个月内注册了多个订单，我想在该月只计算一次，如果他在接下来的几个月注册新订单，我也会在每个新的月份计算他.

Note that if a customer registers more than one order in a month, I want to count him just once for that month and if he registers a new order in the next months, also I count him in each new month.

也许这些图片可以帮助你更好地理解我的问题.

Maybe these pictures help you to understand my question better.

-我的表中没有 count_of_users 和 count_of_customers 列.我应该计算它们.

-I don't count_of_users and count_of_customers columns in my tables. I should calculate them.

用户表:

<头>

user_id	创建日期
1	2017-12-03
2	2018-01-01
3	2018-01-01
4	2018-02-04
5	2018-03-10
6	2018-04-07
7	2018-04-08
8	2018-09-12
9	2018-10-02
10	2018-10-02
11	2018-10-09
12	2018-10-11
13	2018-10-12
14	2018-10-12
15	2018-10-20

客户订单表:

<头>

user_id	订单日期	order_id
1	2018-03-28	120
1	2018-03-28	514
1	2018-03-30	426
2	2018-02-11	125
2	2018-03-01	547
3	2018-02-10	588
3	2018-04-03	111
4	2018-02-10	697
5	2018-04-02	403
5	2018-04-05	321
6	2018-04-09	909
11	2018-10-25	8401

推荐答案

为此，您需要一些构建块.这是我使用的数据模型:

You need a few building blocks for this. Here is the data model I used:

<编辑>我看到不同表中的 user_id 不一样，在这种情况下，您可以省略表之间的关系，并且 Calendar 表中的两个关系都将处于活动状态 - 无需更改关系count_of_customer 度量中的语义.</edit>

<edit> I see user_id in the different tables are not the same, in that case you can omit the relationship between the tables and the two relationships from the Calendar table will both be active - with no need to change the relationship semantics in the count_of_customer measure. </edit>

日历表很重要，因为我们不能依赖单个日期列来聚合来自不同表的数据，因此我们使用此示例 DAX 代码创建一个通用日历表:

The calendar table is important because we can't rely on one single date column to aggregate data from different tables, so we create a common calendar table with this sample DAX code:

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO () ,
    "Year" , YEAR ( [Date] ) ,
    "Month" , FORMAT ( [Date] , "MMM" ) ,
    "Month-Year" , FORMAT ( [Date] , "MMM")&"-"&YEAR ( [Date] ) ,
    "YearMonthNo" , YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1
)

确保按 YearMonthNo 列对 Month-Year 列进行排序，以便您的表格看起来不错:

Make sure to sort the Month-Year column by the YearMonthNo column so your tables look nice:

将您的关系设置为从 Calendar 到 user 的活动关系 - 如果不是，除非您在代码中相应地更改关系，否则这些措施将不起作用！在我的数据模型中，Calendar 和 customer order 之间存在非活动关系.

Set your relationships as shown with the active relationship from Calendar to user - if not the measures will not work unless you alter the relationships accordingly in the code! In my data model the inactive relationship is between Calendar and customer order.

接下来是我们将为此使用的措施.首先我们统计用户数，一个简单的行数:

Next up are the measures we will use for this. First off we count the users, a simple row count:

count_of_users = COUNTROWS ( user )

然后我们统计order表中不同的用户id来统计客户，这里我们需要使用Calendar和customer orderinactive关系> 为此，我们必须调用 CALCULATE:

Then we count distinct user ids in the order table to count customers, here we need to use the inactive relationship between Calendar and customer order and to do this we have to invoke CALCULATE:

count_of_customers = 
CALCULATE (
    DISTINCTCOUNT ( 'customer order'[user_id] ) ,
    USERELATIONSHIP (
        'Calendar'[Date] ,
        'customer order'[order_date]
    )
)

我们可以使用这个度量来累计统计用户数:

We can use this measure to count users cumulatively:

cumulative_users = 
VAR _maxVisibleDate = MAX ( 'Calendar'[Date] ) 
RETURN
CALCULATE ( 
    [count_of_users] , 
    ALL ( 'Calendar' ) , 
    'Calendar'[Date] <= _maxVisibleDate
)

这个衡量每月累积客户的方法:

And this measure to count cumulative customers per month:

cumulative_customers = 
VAR _maxVisibleDate = MAX ( 'Calendar'[Date] ) 
RETURN
CALCULATE ( 
    SUMX ( 
        VALUES ( 'Calendar'[YearMonthNo] ) ,
        [count_of_customers] 
    ),
    ALL ( 'Calendar' ) , 
    'Calendar'[Date] <= _maxVisibleDate
)

最后我们想要这些最后的累积度量的比率:

Lastly we want the ratio of these last cumulative measures:

cumulative_customers/users = 
DIVIDE (  
    [cumulative_customers] , 
    [cumulative_users] 
)

这是你的结果:

这篇关于在 Power BI 中计算累积值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！