具有多个条件的 Power BI 累积计数 [英] Power BI cumulative count with multiple condition

问题描述

有人知道如何将 RunningCount 转换为 Power bi Dax 吗?我在 RunningTotal、Rankx 上进行测试，但似乎无法正常工作.[year] 只是一个文本列，不是日期时间格式.

我还是新手，不确定我的解释是否足够好.如有不便之处，敬请原谅.

我尝试根据下面的公式在 power bi 中创建度量/计算列.我需要按年份计数，按产品、客户、费率和类别等列进行细分.

解决方案

在我进入我的解决方案之前，我必须做出一些假设:

1. 年份"列应该是年份，而不是五位数字.因此，在我的数据中，我删除了每个数据中的第二个2"(即20213"->2013"​​).
2. 您的数据中可能有另一列来打破平局，但鉴于您提供的数据，无法对第一行和第三行进行排名(它们都有产品 ABC 和 2003 年).

鉴于这些假设，这是我的解决方案...

首先，这是我的数据的样子.我添加了一个 ID 列，以便我们可以看到每一行，甚至是重复的.

从那里，您可以使用以下公式简单地添加一个新列.

运行次数 =计数(筛选('数据'，[产品名称] = 早期([产品名称])&&[客户] = EARLIER([客户]) &&[卖家] = EARLIER([卖家]) &&[年份] <= 更早([年份])))

Did anyone know how to convert RunningCount into Power bi Dax ? I test on RunningTotal, Rankx but seems not working. The [year] is just a text column not in Datetime format.

I still new on this and not sure if my explain is good enough or not. Sorry for any inconvenient cause.

I try to create measure/ calculation column in power bi from the formula below. I need the count by Year, break by column like product, customer, rate and category.

解决方案

Before I get into my solution, there are a few assumptions I had to make:

1. The "Year" column is supposed to be years, and not five digit numbers. So in my data I dropped the second "2" in each (i.e. "20213" -> "2013").
2. You may have another column in your data to break ties, but given the data you provided, there is no way to rank the first and third lines (they both have product ABC and year 2003).

Given those assumptions, here is my solution...

First, here is what my data looks like. I added an ID column so that we can see every row, even duplicates.

From there, you can simply add a new column with the following formula.

Running Count = 
    COUNTROWS(
        FILTER(
            'Data',
            [ProductName] = EARLIER([ProductName]) &&
            [Customer] = EARLIER([Customer]) &&
            [Seller] = EARLIER([Seller]) &&
            [Year] <= EARLIER([Year])
        )
    )

The EARLIER function is being used to specify a ProductName, Customer, etc. from the row of the table to be used in the filtering of the data. Once we have the data filtered, we can simple count the number of rows.

The final result looks like this. As noted in my second assumption, there is no way to break ties, so my numbers are slightly off from what you have in your screenshot

这篇关于具有多个条件的 Power BI 累积计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！