Power BI Measure可计算随过滤器变化的百分比 [英] Power BI Measure to calculate percentage which changes with filters

问题描述

我想从数据集中创建可视化效果，以显示使用过滤器时相应变化的百分比.

I want to create a visualisation from a dataset which shows percentages that change accordingly when filters are used.

我有一个像下面这样的数据集，但是有超过100万行的数据涵盖了18个月.所有字段均为文本，除了Month(它是一个日期)和SUMofAPPTS(它是数字)之外.

I have a dataset like the below but with over 1 million rows of data covering 18 months. All the fields are text except Month which is a date and SUMofAPPTS which is numerical.

SUPP    GEOG1   MODE    STATUS  TYPE    TIME    Month   Day    SUMofAPPTS
AA  00D Face    Att     1       1       Day     2018-06 Sun    12
AA  00D Face    Att     1       1       Day     2018-06 Mon    119
AA  00D Face    Att     1       4       Unk     2018-06 Tues   98
BB  00D Tel     DNA     2       1       Day     2018-06 Weds   98
BB  00D Online  DNA     3       1       Day     2018-06 Thurs  126
CC  00D Face    DNA     1       2       Day     2018-07 Sun    8

我想要一种可以通过 Day 和 MODE 来计算 SUMofAPPTS 的百分比的方法(对于 STATUS ， TYPE 和 TIME )，当在其他字段上放置过滤器时，它们会发生变化.

I would like a measure which calculates the percentage of SUMofAPPTS by Day and MODE (and the same but for STATUS, TYPE and TIME) which changes when filters are placed on the other fields.

所以我认为我需要使这种简单的计算更具动态性(如果我只想知道整个数据集的每行百分比，该计算将在列中起作用)，以便在过滤数据时起作用:

So I think I need to make this simple calculation (which would work in a column if I just wanted to know the percentage per row of the whole dataset) more dynamic so that it works when I filter the data:

PERCENT = 'dataset'[SUMofAPPTS]/SUM('dataset'[SUMofAPPTS])

最终结果将是具有以下属性的堆叠条形图:

The end result will be a stacked bar chart with the following attributes:

• 以日为轴
• PERCENT作为价值
• 模式，状态，类型或时间作为图例
• 能够过滤除Day和SUMofAPPTS以外的一个，多个或所有字段

推荐答案

首先，创建一个度量以汇总SUMofAPPTS:

First, create a measure for aggregating SUMofAPPTS:

Total APPTS = SUM(Data[SUMofAPPTS])

第二，为百分比创建度量:

Second, create a measure for the percentage:

APPTS % of Selected 
= DIVIDE(
   [Total APPTS],
   CALCULATE([Total APPTS], ALLSELECTED()))

此度量将重新计算[总APPTS]，忽略除用户(在切片器等)上选择的那些过滤器以外的所有过滤器.

This measure recalculates [Total APPTS] ignoring all filters except those selected by a user (on slicers etc).

结果:

选择后:

修改

如果您需要按天(或其他任何字段)细分，则可以重新引入过滤器，如下所示:

If you need to breakdown by Day (or any other field), you can re-introduce filters like this:

APPTS % of Selected 
= DIVIDE(
   [Total APPTS],
   CALCULATE([Total APPTS], ALLSELECTED(), VALUES(Data[Day])))

这篇关于Power BI Measure可计算随过滤器变化的百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！