功率双计算量度与计算列

    科技2025-03-28  21

    Microsoft’s Power BI has drastically improved over the past 3 years. Starting as a weak competitor in the data visualization space, its monthly updates have gradually transformed it into a strong contender against Tableau, TIBCO’s Spotfire, and Qlik. However, even with major improvements, some confusing features remain.. One of the most commonly asked questions I get about Power BI involves calculated measures and calculated columns.

    在过去的三年中,Microsoft的Power BI取得了巨大的进步。 从数据可视化领域的薄弱竞争对手开始,它的每月更新逐渐将其转变为与Tableau,TIBCO的Spotfire和Qlik竞争的有力竞争者。 但是,即使进行了重大改进,仍然存在一些令人困惑的功能。我对Power BI提出的最常见的问题之一是计算的度量和计算的列。

    We all leverage calculations in our data models and visualizations. So, these two features are front and center. When understood properly, they will enrich your data model and the insights you share through your reports and dashboards.

    我们都在数据模型和可视化中利用计算。 因此,这两个特征是正面和中间。 如果正确理解,它们将丰富您的数据模型以及您通过报告和仪表板共享的见解。

    These two features sound similar, if not the same, and both can be accessed from the same location on the ribbon within Power BI. They are often used interchangeably, and subsequent chaos ensues because they are not interchangeable. The differences between them are subtle but significant.

    这两个功能听起来很相似,即使不相同,也可以从Power BI中功能区上的相同位置访问这两个功能。 它们经常可互换使用,并且随后出现混乱,因为它们不可互换。 它们之间的差异是细微但重要的。

    计算列 (Calculated Columns)

    A calculated column is an extension of a table that’s evaluated for each row. Calculated columns live in the xVelocity in-memory storage of Power BI, just like all the other data you import from a data source. A calculated column is virtually the same as a non-calculated column, with one exception. Their values are calculated using DAX formulas and values from other columns.

    计算列是对每行评估的表的扩展。 计算列存储在Power BI的xVelocity内存中,就像您从数据源导入的所有其他数据一样。 计算列实际上与非计算列相同,唯一的例外是。 它们的值是使用DAX公式和其他列中的值计算的。

    Since calculated columns live at the same level as your tables, they are only calculated when you first define them and during a dataset refresh. An example of a calculated column is deriving the per-unit profit of a product by subtracting the product's cost from the price of the product.

    由于计算的列与表处于同一级别,因此仅在您首次定义它们时以及在数据集刷新期间才计算它们。 计算列的一个示例是通过从产品价格中减去产品成本来得出产品的单位利润。

    Per Unit Profit = Products[Price] — Products[Cost]

    每单位利润=产品[价格] —产品[成本]

    This calculation will be performed for each row in the Products table. The calculated value will be stored in the new Per Unit Profit field.

    将对“产品”表中的每一行执行此计算。 计算出的值将存储在新的“每单位利润”字段中。

    How to:

    如何:

    Select the Product Table in the FIELDS PANE.

    在FIELDS PANE中选择产品表。

    2. From the Modeling tab, Calculations section, select New Column.

    2.从“建模”选项卡的“计算”部分,选择“新建列”。

    3. In the formula bar, enter the above formula.

    3.在编辑栏中,输入上面的公式。

    Calculated columns can also be created by right-clicking on the table name in the FIELDS PANE and selecting New Column from the context menu. You can also create a calculated column from within the Power BI Query Editor.

    也可以通过右键单击FIELDS PANE中的表名称并从上下文菜单中选择“新建列”来创建计算列。 您也可以在Power BI查询编辑器中创建一个计算列。

    计算量度 (Calculated Measures)

    A calculated measure is not treated the same as any other table column. Unlike a calculated column, measures are evaluated on the fly with every change in context. If you change a date filter from 2019 to 2020, all the measures will be calculated again. While this is nice for seeing context-based calculations in real-time, it becomes demanding on your computer’s processor.

    计算得出的度量与任何其他表列都不相同。 与计算列不同,度量会随上下文的变化而动态评估。 如果您将日期过滤器的日期从2019更改为2020,则会重新计算所有度量。 尽管这对于实时查看基于上下文的计算非常有用,但对计算机处理器的要求却很高。

    A measure is also intended for use as an aggregation. Every calculated measure must contain an aggregation function such as AVG or SUM. Without an aggregation function, the measure’s formula will display an error.

    度量也旨在用作汇总。 每个计算得出的度量必须包含聚合函数,例如AVG或SUM。 如果没有聚合函数,则度量的公式将显示错误。

    How to:

    如何:

    Select the Product Table in the FIELDS PANE.

    在FIELDS PANE中选择产品表。

    2. From the Modeling tab, Calculations section, select New Measure.

    2.从“建模”选项卡的“计算”部分,选择“新度量”。

    3. In the formula bar, enter the above formula.

    3.在编辑栏中,输入上面的公式。

    何时使用计算列和度量 (When to Use Calculated Columns and Measures)

    There’s no hard rule for choosing one over the other. In some instances, you can use either a calculated column or a measure. However, there are situations when only one option will work. Remember, if you can use either, a calculated column will consume fewer resources while your users interact with your report. Measures recalculate each time the user changes a filter and can cause your report to respond slowly.

    没有一个硬性规则可以选择一个。 在某些情况下,您可以使用计算列或度量。 但是,在某些情况下,只有一种选择会起作用。 请记住,如果您可以使用任一方法,则在用户与报表进行交互时,计算所得的列将消耗较少的资源。 每当用户更改过滤器时,度量值都会重新计算,并且可能导致报告响应缓慢。

    Another consideration when using calculated measures is the calculated column’s inability to reference them in a formula. Since measures are considered dynamic and outside the xVelocity tables, calculated columns should not use them in their definition. This frequently muddles the efforts of Power BI newcomers. They begin by creating a measure for their highly context-driven report. As the requests for new report features increase, the need arises for more calculated columns. Frustration strikes along with the realization that your measures are in a world by themselves and are useless from the calculated column formula bar.

    使用计算量度时的另一个考虑因素是计算列无法在公式中引用它们。 由于度量被认为是动态的并且在xVelocity表之外,因此计算列不应在其定义中使用它们。 这经常会混淆Power BI新手的工作。 他们从为高度依赖上下文的报告创建度量开始。 随着对新报表功能的需求增加,需要更多的计算列。 挫败感随之而来,同时意识到您的度量标准本身就是一个世界,并且从计算得出的列公式栏中毫无用处。

    One scenario solved only by a calculated measure is needing the calculation to change anytime the context changes. If I want the sales person’s percentage of sales to change for the selected region, it needs to be recalculated based on the total sales per region. That should happen each time the user selects another region in the slicer.

    仅通过计算的量度解决的一种情况是,只要上下文发生变化,就需要更改计算。 如果要更改所选区域的销售人员的销售百分比,则需要根据每个区域的总销售额重新计算。 每当用户在切片器中选择另一个区域时,就会发生这种情况。

    Conversely, sometimes you can only use a calculated column. Measures cannot be used as filters in a slicer nor as filters at page and report levels. In these instances, if you are using a calculation as the source of your filter, you must use a calculated column.

    相反,有时您只能使用计算列。 度量不能用作切片器中的过滤器,也不能用作页面和报告级别的过滤器。 在这些情况下,如果您使用计算作为过滤器的来源,则必须使用计算列。

    I hope this overview of the differences between calculated columns and calculated measures in Power BI is helpful. Even with this explanation, adapting to them and understanding when to use them in their proper spaces can take time. Hopefully, it will happen a little faster for you now. But don’t give up. In the end, you’ll be pleased with their power and usefulness.

    我希望对Power BI中计算列与计算量度之间的差异的概述有所帮助。 即使有了这种解释,适应它们并了解何时在适当的空间中使用它们仍会花费一些时间。 希望现在它会为您带来更快的速度。 但是不要放弃。 最后,您将对它们的功能和实用性感到满意。

    Rod Castor helps companies Get Analytics Right! He works with international organizations and small businesses to improve their data analytics efforts, data science, tech strategy, and tech leadership. In addition to consulting, Rod also enjoys public speaking, teaching, and writing. You can discover more about Rod and his work at rodcastor.com and appliedai.us.

    Rod Castor 帮助公司正确完成分析! 他与国际组织和小型企业合作,以改善他们的数据分析工作,数据科学,技术战略和技术领导力。 除了提供咨询服务外,Rod还喜欢公开演讲,教学和写作。 你可以发现更多关于罗德和他的工作 rodcastor.com 和 appliedai.us 。

    翻译自: https://towardsdatascience.com/power-bi-calculated-measures-vs-calculated-columns-9be012e9bff1

    相关资源:雷达散射截面的计算
    Processed: 0.013, SQL: 8