创建度量以根据按日期查找来计算值 [英] Create Measure to Calculate Value Based on Lookup By Date

问题描述

我正在尝试创建一种措施，该措施可以按日期给我一个基于整体预期百分比的值.这将使我创建一个视觉效果，以显示预期和实际执行值.我没有计算实际值，但是预期计算有问题.

I am trying to create a measure that would give me a value based on an expected percentage of of the whole, by date. This will let me create a visual showing expected and actual execution values. I have the actual value calculation down, but I am having issues with the expected calculation.

按日期设置了具有预期百分比的表

The table with the expected percentage by date is setup with columns

Date, Project, Expected Pct Complete
1/1/2019, ProjA, .20
1/2/2019, ProjA, .40
1/3/2019, ProjA, .60
1/4/2019, ProjA, .80
1/5/2019, ProjA, 1.00
1/1/2019, ProjA, .33
1/2/2019, ProjA, .66
1/3/2019, ProjA, 1.00

然后我有一个包含所有测试执行情况的表，其中的数据具有这种常规格式，

I then have a table with all the test executions, where the data has this general format,

Execution Date, Project, Script, Status
1/1/2019, ProjA, Script1, Passed 
1/1/2019, ProjB, ScriptA, Failed
1/1/2019, ProjA, Script2, Failed
1/2/2019, ProjA, Script3, Passed

我希望度量生成表格的值(假设项目A和B都有100个脚本)

I want the measure to generate values of form (assume project A & B both have 100 scripts)

Date, Expected Amount, Project
1/1/2019, 20, ProjA
1/1/2019, 33, ProjB
1/2,2019, 40, ProjA
1/2/2019, 66, ProjB
1/3/2019, 60, ProjA
1/3/2019, 100, ProjB
1/4/2019, 80, ProjA
1/5/2019, 100, ProjA

当我从切片器中选择特定项目时，如何创建此度量以便将其放入可视化视图中，以向我显示实际利率与预期利率之间的关系?

How can I create this measure so that I can put it into a visualization that will show me that actual vs expected rates, when I select the particular project from a slicer?

推荐答案

尝试使用以下代码将计算所得的列添加到帖子的第一个表中.

Try adding a calculated column to the first table in your post with the following code.

Expected Amount = 
    var _totalScriptsPerProject = 
        CALCULATE ( 
            DISTINCTCOUNT ( tests[Script] ) ; 
            FILTER ( ALL ( tests ) ; tests[Project] = expectations[Project] )
        )
RETURN
    expectations[Expected pct Complete] * _totalScriptsPerProject

(请注意，我假设第一个表称为期望"，第二个表是"tests"，也请注意，我使用分号作为参数分隔符)

(note I assumed the first table to be called 'expectations' and the second 'tests', also note I use semicolons as argument seprator)

这篇关于创建度量以根据按日期查找来计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！