DAX按测量结果分组 [英] DAX grouping by a measure result

问题描述

在这里需要一些帮助，以便使用DAX在PowerBI中将视觉测量结果分组。我有一个表，显示某些客户的所有访问（访问表）。我有一个衡量每天访问量的指标，并且有一个日期切片器（访问日）。该度量的结果通常在1到10之间。我试图查看每年客户访问的频率。如果他们有

Need some help here grouping a measure result for a visual in PowerBI using DAX. I have a table that displays all visits by certain customers (Visits table). I have a measure that counts the number of visits for each day, and I have a date slicer (visit day). The results of this measure usually range between 1 and 10. I am trying to see a frequency of customers visits per year. If they have

1 = single visit
3 or more visits = returning customer
7 or more visits = frequent customer.

我正在尝试使用饼图/甜甜圈图显示一年内客户的细分情况。我相信这是可能的，但我缺少一些东西。任何帮助将不胜感激！

I am trying to use the pie chart/ donut chart to display the breakdown of my customers within a year. I believe this is possible, but I am missing something. Any help would be appreciated!

Customer_id          Visit_date      Purchase_total   

以上是访问表的示例。还有一种使用DAX的方法来获取每天的总访问量。 （某些客户在给定的一天[visits_day]内多次出现。）

The above is a sample of the visits table. There is also a measure that uses DAX to get the Total Visits Per Day. (Some customers come in multiple times during a given day [visits_day].)

推荐答案

在饼图中按度量进行分组是有点棘手，因为您无法对例如图例框，如果希望根据切片器的选择更改分组，则不能使用计算列。

Grouping by a measure in a pie chart is a bit tricky since you can't put a measure into e.g. the Legend box and you can't use a calculated column instead if you want the grouping to change based on your slicer selections.

以下是一种可能的解决方法：

Here's one possible workaround:

首先，为您的类别存储桶创建一个新的存储桶表。

First, create a new Buckets table for your category buckets.

Bucket
------
single visit
returning customer
frequent customer

此 Buckets [Bucket] 列将在图例部分中使用。

This Buckets[Bucket] column is what you'll use in your Legend section.

对于值部分，我们需要一种新的度量标准：

For the Values section, we need a new measure:

Count of Visits =
VAR Summary =
    SUMMARIZE (
        Visits,
        Visits[Customer_id],
        "Bucket", SWITCH (
            TRUE (),
            COUNTROWS ( Visits ) >= 7, "frequent customer",
            COUNTROWS ( Visits ) >= 2, "returning customer",
            COUNTROWS ( Visits ) = 1, "single visit"
        )
    )
RETURN
    SUMX ( Summary, IF ( [Bucket] = SELECTEDVALUE ( Bucket[Bucket] ), 1, 0 ) )

此度量通过根据访问客户的次数将每个客户放入三个存储桶之一来汇总当前 Visits 表（已应用切片器过滤）您选择的日期范围。然后，我们为汇总表中与其存储桶与当前饼形图存储桶相匹配（且<$ c与存储桶相匹配）的客户添加 1 来统计属于当前饼形图部分的客户数量$ c> 0 否则。）

This measure summarizes the current Visits table (with any slicer filtering applied) by putting each customer into one of the three buckets based on the number of times they've visited in your selected date range. Then we count how many customers fall into the current pie chart section by adding 1 only for the customers in the summary table whose bucket matches the current pie chart bucket (and 0 otherwise).

注意：此度量对您的客户进行了分类您选择的日期。如果您不在乎，只需要计算固定时间段的类别（即，您不希望日期切片器更改其所属的存储桶），则可以使用计算列代替需要创建一个新表。

Note: This measure categorized your customers for the dates you have selected. If you don't care about that and only need to calculate their category for fixed period(s) (i.e. you don't want your date slicer to change the bucket they belong to), then you can just use a calculated column instead of needing to create a new table.

这篇关于DAX按测量结果分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！