如何计算DAX中的累计总计和％？ [英] How to calculate cumulative Total and % in DAX?

问题描述

这可能非常简单...

我在Power BI中具有以下摘要表，需要构建帕累托图，我要寻找的是一种创建列 D和 E的方法...预先感谢！

B列中的计数是我在PBI中创建的一种度量基于多个过滤器。我已经尝试过一些Calculate / Sum / Filter类型的表达式，但是没有运气。

我的原始数据看起来像图片2。 ..除了列 I-运行％-（我还需要每个存储桶的事件总数）之外，我有措施来构建汇总表。

不幸的是，我无法成功应用DAXPATTERNS的计算。

解决方案

条形图可视化

请注意，我的表达式使用 EventTable ，应将其替换为表名。另外请注意运行％线从0开始到1，并且左侧只有一个Y轴。

让我知道这是否有帮助。

This might be very simple...

I have the below summary table in Power BI and need to build a Pareto Chart, what I'm looking for is a way to create columns "D" and "E"... Thanks in advance!

The Count from column "B" is a measure I've created in PBI based on multiple filters. I've already tried some Calculate/Sum/Filter type of expressions with no luck.

My raw data looks like Image #2... I have the measures to build the summary table with the exception of column "I" - Running % - (for which I will also need the cumulative total of events per bucket).

Unfortunately, I haven't been able to successfully apply the calculations from DAXPATTERNS.

解决方案

There is a well-known pattern for cumulative calculations in the DAXPATTERNS blog.

Try this expression for Running % measure:

Running % =
CALCULATE (
    SUM ( [Percentage] ),
    FILTER ( ALL ( YourTable), YourTable[Bucket] <= MAX ( YourTable[Bucket] ) )
)

And try this for Cumulative count measure:

Cumulative Count =
CALCULATE (
    SUM ( [Count] ),
    FILTER ( ALL ( YourTable ), YourTable[Bucket] <= MAX ( YourTable[Bucket] ) )
)

Basically in each row you are summing those count or percent values that are less or equal than the bucket value in the evaluated row, which produces the cumulative total.

UPDATE: A posible solution matching your model.

Assuming your Event Count measure is defined as follows:

Event Count = COUNT(EventTable[Duration_Bucket])

You can create a cumulative count using CALCULATE function, which lets us calculate the Running % measure:

Cumulative Count =
CALCULATE (
    [Event Count],
    FILTER (
        ALL ( EventTable ),
        [Duration_Bucket] <= MAX ( EventTable[Duration_Bucket] )
    )
)

Now calculate the Running % measure using:

Running % =
DIVIDE (
    [Cumulative Count],
    CALCULATE ( [Event Count], ALL ( EventTable ) ),
    BLANK ()
)

You should get something like this in Power BI:

Table visualization

Bar chart visualization

Note my expressions use an EventTable which you should replace by the name of your table. Also note the running % line starts from 0 to 1 and there is only one Y-axis to the left.

Let me know if this helps.

这篇关于如何计算DAX中的累计总计和％？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！