Power BI-测量以计算不重复计数 [英] Power BI - Measure to calculate the distinct count
问题描述
我有一个要求,我要在计算不重复计数的同时保留特定值。
例如,
请考虑此-
BU Rev RevDes串联的columnfordistinctcount
1A AppR 1A 1AAppR1A
1A AppR 2A 1AAppR2A
1A FAppR 3A 1AFAppR3A
2A BcR 1A 2ABcR1A
2A BcR 1A 2ABcR1A
2A AcR 1A 2AAcR1A
对于层次结构,想象一下类似的东西-
-
BU-大父母
-
RevDes-父母
-
Rev-儿童
第一步–计算串联输出列的不重复计数
对于业务部门1A –将会是3
对于业务部门2A,它将是2
第二步-che ck是否在某个业务部门中使用Rev Des – FAppR。
在1A业务部中存在FAppR-因此它变为1。
业务部门2A中不存在FAppR-因此保持不变-2。
最终输出:-
因此,同时选择两个业务部门1A和2A的最终输出为1 + 2 = 3。
预期的总输出为 3
您不需要串联的列。您可以通过两种措施来做到这一点:
UniqueCount =
IF(
COUNTROWS(
FILTER(
Table1,
Table1 [Rev] = FAppR
)
)> 0,
1,
COUNTROWS(
GROUPBY(
Table1,
Table1 [BU],
Table1 [Rev],
Table1 [RevDes]
)
)
)
和
UniqueSum =
SUMX(
DISTINCT(Table1 [BU]),
[UniqueCount]
)
将 UniqueSum
度量用于所需的输出。
工作示例PBIX文件: https://pwrbi.com/so_54693457/
I have a requirement where I am calculating the distinct count while leaving a particular value.
For example,
Consider this -
BU Rev RevDes concatenatedcolumnfordistinctcount
1A AppR 1A 1AAppR1A
1A AppR 2A 1AAppR2A
1A FAppR 3A 1AFAppR3A
2A BcR 1A 2ABcR1A
2A BcR 1A 2ABcR1A
2A AcR 1A 2AAcR1A
For hierarchy, Imagine something like this -
BU - Grand Parent
RevDes - Parent
Rev - Child
First Step – Calculate the distinct count of the concatenated output column
For Business Unit, 1A – it will be 3
For Business Unit 2A – it will be 2
Second Step - check whether Rev Des – FAppR is present for a business unit.
FAppR is present for Business Unit 1A - so it becomes 1.
FAppR is not present for Business Unit 2A - so it stays the same - 2.
Final output :-
So the final output when both business units 1A and 2A are selected is 1 + 2 = 3.
The total output expected is 3
You don't need the concatenated column. You can do this with two measures:
UniqueCount =
IF (
COUNTROWS (
FILTER (
Table1,
Table1[Rev] = "FAppR"
)
) > 0,
1,
COUNTROWS (
GROUPBY (
Table1,
Table1[BU],
Table1[Rev],
Table1[RevDes]
)
)
)
and
UniqueSum =
SUMX (
DISTINCT ( Table1[BU] ),
[UniqueCount]
)
Use the UniqueSum
measure for your required output.
Worked example PBIX file: https://pwrbi.com/so_54693457/
这篇关于Power BI-测量以计算不重复计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!