按度量结果进行 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:

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

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 表(应用了任何切片器过滤).然后我们只为汇总表中桶与当前饼图桶匹配的客户添加 1 来计算有多少客户落入当前饼图部分(否则为 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屋！