Power BI Measure 计算随过滤器变化的百分比

本文介绍了Power BI Measure 计算随过滤器变化的百分比的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

我有一个如下所示的数据集，但包含 18 个月的超过 100 万行数据.除了 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 作为值
• MODE、STATUS、TYPE 或 TIME 作为图例
• 能够按除 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()))

此度量会重新计算 [Total 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])))