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

问题描述

这可能很简单……

我在 Power BI 中有以下汇总表，需要构建一个帕累托图，我正在寻找一种创建列D"和E"的方法......提前致谢！

B"列的计数是我在 PBI 中基于多个过滤器创建的度量.我已经尝试了一些计算/求和/过滤类型的表达式，但没有成功.

我的原始数据看起来像图片 #2... 我有构建汇总表的措施，但列I"除外 - 运行百分比 - (为此我还需要每个存储桶的累积事件总数).

很遗憾，我无法成功应用来自 DAXPATTERNS 的计算.

解决方案

在

条形图可视化

请注意，我的表达式使用了一个 EventTable，您应该将其替换为您的表的名称.还要注意 running % 行从 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屋！