Power BI：计算无法正常工作 [英] Power BI: Calculations not working properly
问题描述
我有一个表，其中包含资源名称，资源所有者，项目名称，项目经理，工作量。
对于每个资源所有者，我都想计算出借出的努力（由所有者的资源在除所有者的项目之外的项目中花费的努力）和借入的努力（由所有者的资源在其他项目中的所有权所花费的努力，项目所有者除外）。
例如，资源所有者= X
项目经理= AllExcept（X）
总和（努力） =借出时间
资源所有者= AllExcept（X）
项目经理= X
总和（工作量）=借入。
为了计算借出的小时数，我正在使用此DAX公式-
贷款= IF（ISBLANK（CALCULATE（SUM（'All_Activity Data'[Logged Effort]），FILTER（'All_Activity Data'，'All_Activity Data'[Project Manager] <>'All_Activity Data'[资源所有者]）））），0，CALCULATE（SUM（'All_Activity Data'[记录的工作量]），FILTER（'All_Activity Data'，'All_Activity Data'[项目经理]<> ;''All_Activity Data'[资源所有者]））））
为了计算借入，我使用的是-
借入= IF（ISBLANK（CALCULATE（SUM（'All_Activity Data'[Logged Effort]）），FILTER（'All_Activity Data'，' All_Activity Data'[Project Manager]<>'All_Activity Data'[Resource Owner]），FILTER（'All_Activity Data'，'All_Activity Data'[Project Manager] ='All_Activity Data'[Resource Owner]））））），0 ，CALCULATE（SUM（'All_Activity Data'[Logged Effort]），FILTER（'All_Activity Data'，'All_Activity Data'[Project Manager]<>'All_Activity Data '[资源所有者]），FILTER（'All_Activity数据'，'All_Activity数据'[项目经理] ='All_Activity数据'[资源所有者]））））
贷款时间计算正确，但没有被借出。任何帮助将不胜感激。
以下是示例数据：
RN Eff PN P_M RO
名称1 1.50 AOX
名称2 8.00 BNS
名称1 5.50 CNS
名称2 1.50 DSS
名称1 5.00 EXS
名称2 8.00 FNN
名称1 4.00 GLX
名称2 4.00 HLX
名称1 0.25 IXN
名称2 0.25 GOX
名称1 0.25 AXD
名称2 6.75 BNX
名称1 0.50 CXX
名称2 8.00 DNL
名称1 0.17 EDD
名称2 2.26 FDD
名称1 1.58 GLD
名称2 2.50 HDD
名称2 0.58 IDD
名称1 0.33 GDD
名称2 0.34添加
名称1 0.25 BDD
名称2 6.00 CDS
名称1 0.30 DNS
名称1 8.00 ENN
名称2 2.00 FSS
名称1 4.50 GSS
名称2 1.50 HSS
NAM E 1 2.00 ISS
名称2 1.50 GSS
名称2 4.50 ASS
名称2 8.00 BSS
名称2 8.00 CSL
名称2 8.00 DNS
名称2 8.00 ESL
名称2 8.00 FSL
名称2 8.00 GOX
名称2 1.25 HLL
名称2 2.75 ILL
名称1 0.75 GLL
名称1 1.50 ALL
名称1 0.75 BOL
名称1 1.25 COX
名称1 0.50 DNX
名称1 1.00 EXX
名称1 2.00 FOX
名称1 2.50 GSN
名称1 7.00 HSN
名称1 1.50 ILN
名称2 6.50 GLN
名称2 8.00 ANS
名称2 8.00 BNX
名称2 7.50 CLS
名称1 3.25 DNN
名称1 2.25 ENN
名称1 0.75 FNN
名称2 0.75 GNN
名称1 1.00 HLX
名称1 4.00 ISX
名称1 3.00 GNX
名称2 8.0 0全部
名称1 6.00 BNN
名称1 0.50 CNN
名称1 0.50 DNN
名称2 7.00 ENX
名称1 0.25 FOX
名称1 0.50 GOX
名称1 0.25 HXX
在这种情况下-
借给X PM是-52
借入是-5.5
问题中有3种类型的努力：
最简单的工作类型是非借用或借用工作-您关心的经理既是资源所有者又是项目经理（即他们在他们管理的项目上使用他们管理的资源）。
下一种类型的工作是贷款工作-您关心的经理是资源所有者，而不是项目经理（即正在使用经理的资源）
最后一种工作是借来的工作-您关心的经理是项目经理，而不是资源所有者（即他们是
您已正确定义了工作量，但这是基于资源所有者是您关心的经理的假设。确实，您的贷款努力计算对于借来的努力也是完全正确的，除了您需要由项目经理而不是资源所有者显示表（这是因为项目经理会借用和资源所有者出借-否则计算相同。）
因此，问题的本质不是计算上的问题，而是因为您要同时显示同一个人的借入和借出数字。
为此，我建议使用一个单独的重复数据删除管理器表，您可以为该表计算借入和借出的数量。这样做的主要原因之一是因为经理可以借贷但从不借贷。如果从资源所有者（谁借出）为前提开始，借用但从不借出的经理将永远不会出现在您的结果中。
我将此表称为DimManager，其中单个列 Manager 。您显然可以对此进行更改。我没有不将此表与All_Activity数据相关：
然后，对于这些措施，我从不借贷开始，我希望经理既是项目经理又是资源所有者：
未借出或借入=
SUMX（
'DimManager'，
CALCULATE（
SUMX（
FILTER（
'All_Activity Data'，
'All_Activity Data'[Project Manager] = MAX（'DimManager'[Manager]）
&&'All_Activity Data'[Resource Owner] = MAX（'DimManager '[经理]）
），
'所有活动数据'[记录的工作量]
）
）
）
由内而外地工作，FILTER是在两个不相关的表。在这种情况下，我希望经理既是项目经理又是资源所有者。嵌套的SUMX（CALCULATE（SUMX使得在计算列总计时，它对每个单独的管理器的结果求和。如果没有此值，则该度量将使用MAX（Manager）作为列总计，使列的总计与经理X在您的样本数据中。我从Matt Allington中学到了这个技巧：
I have a table containing Resource Name, Resources Owner, Project Name, Project Manager, Effort. For Every resource owner, I want to calculate the loaned out efforts(efforts spent by resources of owner in projects other than owner's project) and borrowed efforts (efforts spent by resources of owner in other's project except the project owner's).
For e.g., Resource Owner = X
Project Manager = AllExcept(X)
Sum(Effort) = Loaned Out Hours
Resource Owner = AllExcept(X)
Project Manager = X
Sum(Effort) = Borrowed.
For calculating loaned out hours I am using this DAX Formula-
Loaned = IF(ISBLANK(CALCULATE(SUM('All_Activity Data'[Logged Effort]), FILTER('All_Activity Data','All_Activity Data'[Project Manager] <> 'All_Activity Data'[Resource Owner]))),0,CALCULATE(SUM('All_Activity Data'[Logged Effort]),FILTER('All_Activity Data','All_Activity Data'[Project Manager] <> 'All_Activity Data'[Resource Owner])))
For calculating borrowed I am using -
Borrowed = IF(ISBLANK(CALCULATE(SUM('All_Activity Data'[Logged Effort]),FILTER('All_Activity Data','All_Activity Data'[Project Manager] <> 'All_Activity Data'[Resource Owner]), FILTER('All_Activity Data','All_Activity Data'[Project Manager] = 'All_Activity Data'[Resource Owner]))),0,CALCULATE(SUM('All_Activity Data'[Logged Effort]),FILTER('All_Activity Data','All_Activity Data'[Project Manager] <> 'All_Activity Data'[Resource Owner]), FILTER('All_Activity Data','All_Activity Data'[Project Manager] = 'All_Activity Data'[Resource Owner])))
Loaned hours are calculating properly but not borrowed out. Any help would be appreciated.
Here is sample data:
RN Eff PN P_M RO
NAME 1 1.50 A O X
NAME 2 8.00 B N S
NAME 1 5.50 C N S
NAME 2 1.50 D S S
NAME 1 5.00 E X S
NAME 2 8.00 F N N
NAME 1 4.00 G L X
NAME 2 4.00 H L X
NAME 1 0.25 I X N
NAME 2 0.25 G O X
NAME 1 0.25 A X D
NAME 2 6.75 B N X
NAME 1 0.50 C X X
NAME 2 8.00 D N L
NAME 1 0.17 E D D
NAME 2 2.26 F D D
NAME 1 1.58 G L D
NAME 2 2.50 H D D
NAME 2 0.58 I D D
NAME 1 0.33 G D D
NAME 2 0.34 A D D
NAME 1 0.25 B D D
NAME 2 6.00 C D S
NAME 1 0.30 D N S
NAME 1 8.00 E N N
NAME 2 2.00 F S S
NAME 1 4.50 G S S
NAME 2 1.50 H S S
NAME 1 2.00 I S S
NAME 2 1.50 G S S
NAME 2 4.50 A S S
NAME 2 8.00 B S S
NAME 2 8.00 C S L
NAME 2 8.00 D N S
NAME 2 8.00 E S L
NAME 2 8.00 F S L
NAME 2 8.00 G O X
NAME 2 1.25 H L L
NAME 2 2.75 I L L
NAME 1 0.75 G L L
NAME 1 1.50 A L L
NAME 1 0.75 B O L
NAME 1 1.25 C O X
NAME 1 0.50 D N X
NAME 1 1.00 E X X
NAME 1 2.00 F O X
NAME 1 2.50 G S N
NAME 1 7.00 H S N
NAME 1 1.50 I L N
NAME 2 6.50 G L N
NAME 2 8.00 A N S
NAME 2 8.00 B N X
NAME 2 7.50 C L S
NAME 1 3.25 D N N
NAME 1 2.25 E N N
NAME 1 0.75 F N N
NAME 2 0.75 G N N
NAME 1 1.00 H L X
NAME 1 4.00 I S X
NAME 1 3.00 G N X
NAME 2 8.00 A L L
NAME 1 6.00 B N N
NAME 1 0.50 C N N
NAME 1 0.50 D N N
NAME 2 7.00 E N X
NAME 1 0.25 F O X
NAME 1 0.50 G O X
NAME 1 0.25 H X X
In this case- Loaned out For X PM is - 52 Borrowed in is - 5.5
There are 3 types of effort in your question:
The easiest type of effort is "not loaned or borrowed effort" - where the manager you care about is both the resource owner and the project manager (i.e. they are using resources they manage on a project they manage).
The next type of effort is "loaned effort" - where the manager you care about is the resource owner but not the project manager (i.e. the manager's resources are being used on someone else's project).
The final type of effort is "borrowed effort" - where the manager you care about is the project manager but not the resource owner (i.e. they are using someone else's resources on their project).
You have loaned effort defined correctly, but it's based on the assumption that the resource owner is the manager you care about. Indeed, your Loaned Effort calculation is perfectly correct for Borrowed Effort too, except you would need to display the table by Project Manager instead of Resource Owner (this is because Project Managers borrow and Resource Owners lend - but otherwise it's the same calculation).
The essence of your problem is, therefore, not to a problem with the calculation, but rather because you want to display the "borrowed" and "loaned" number side-by-side for the same person.
To accomplish this, I recommend having a separate, de-duped table of managers that you can calculate the borrowed and loaned number for. One of the main reasons for doing this is because a manager may borrow but never lend. If you start with the premise of resource owners (who lend), the manager who borrows but never lends will never appear in your results.
I called this table DimManager, with a single column Manager. You can obviously vary this. I did not relate this table to All_Activity Data:
Then, for the measures, I started with not loaned or borrowed, where I want the manager to be both the project manager and the resource owner:
Not Loaned or Borrowed =
SUMX (
'DimManager',
CALCULATE (
SUMX (
FILTER (
'All_Activity Data',
'All_Activity Data'[Project Manager] = MAX ( 'DimManager'[Manager] )
&& 'All_Activity Data'[Resource Owner] = MAX ( 'DimManager'[Manager] )
),
'All_Activity Data'[Logged Effort]
)
)
)
Working from the inside out, the FILTER is what is doing the join between the two unrelated tables. In this case saying that I want the Manager to be both the Project Manager and the Resource Owner. The nested SUMX(CALCULATE(SUMX is so that, when calculating a column total, it sums the result of each individual manager. Without this, the measure would use the MAX(Manager) for the column totals, making the column totals the same as Manager X in your sample data. I learnt about this trick from Matt Allington: https://exceleratorbi.com.au/double-calculate-solves-sumx-problem/
For your loaned and borrowed calculations, they are exactly the same as above, except instead of saying you want both your Resource Owner and Project Manager to match your manager, one of them does not match your manager:
Loaned =
SUMX (
'DimManager',
CALCULATE (
SUMX (
FILTER (
'All_Activity Data',
'All_Activity Data'[Project Manager] <> MAX ( 'DimManager'[Manager] )
&& 'All_Activity Data'[Resource Owner] = MAX ( 'DimManager'[Manager] )
),
'All_Activity Data'[Logged Effort]
)
)
)
'
Borrowed =
SUMX (
'DimManager',
CALCULATE (
SUMX (
FILTER (
'All_Activity Data',
'All_Activity Data'[Project Manager] = MAX ( 'DimManager'[Manager] )
&& 'All_Activity Data'[Resource Owner] <> MAX ( 'DimManager'[Manager] )
),
'All_Activity Data'[Logged Effort]
)
)
)
This results in the final result below:
这篇关于Power BI：计算无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
