sql-server - 根据月度目标计算每日目标 Sales Power bi

Question

我遇到了以下问题，我无法全神贯注地以一种巧妙的方式来解决这个问题。

我想创建一个包含三条线的折线图。我们称之为预算蛇。

• 创建销售订单(黑色)
• 发票订单(绿色)
• 每日目标(红色)

每个销售人员。为已创建和已开票的订单创建此图很容易，因为这些都是按日粒度计算的，因此创建折线图很容易。

我只是在纠结如何为目标创建/生成这样一条线。在这种情况下，我手动创建了一个表，日期 - 销售人员 - 每日目标例如。

这很麻烦。我想做的是为每个销售人员创建一个每月级别的表，然后PowerBI 可以通过以下方式“生成/计算”每日目标我可以绘制红线而无需创建它的所有麻烦每个销售人员手动。

输入应该是这样的

+-----------+----------+--------------+--------+----------------+--------------+---------------+
|   Date    |  Month   | Salesperson  | Branch | Monthly Target | Daily Target | Business days |
+-----------+----------+--------------+--------+----------------+--------------+---------------+
| 1/01/2017 | January  | salesperson1 | test   |          73529 |         4325 |            17 |
| 1/02/2017 | February | salesperson1 | test   |          73529 |         4325 |            20 |
+-----------+----------+--------------+--------+----------------+--------------+---------------+

我有一个日期维度表，所以在我的图表上，我将日期作为 x 轴，然后将 runningorders/runningsales 作为 y 轴，但我想要一个类似每日运行目标的东西，以便红线很好地配合订单和销售额。

我看过这个模式，但我就是想不通它是如何生成的折线图。

https://www.daxpatterns.com/budget-patterns/

所以不知何故，我想我需要一些东西来生成第一个表并将第二个表作为输入。我在 Dax 中尝试了一些措施，但没有一个给我每天的累积步数。它主要只是向我展示了值(value)。

这些是我用于其他线路的措施。这在更改日期过滤器时效果很好。运行销售

RunningTotalSales = CALCULATE(sum(vw_invoice_trn_summary[NetInvoiceValue]),
                     FILTER(ALLSELECTED(DimTime),DimTime[Date] <= MAX(DimTime[Date])))

运行命令

RunningTotalOrders = CALCULATE(sum(vw_orders_raised[OrderTotal]),FILTER(ALLSELECTED(DimTime),DimTime[Date] <= MAX(DimTime[Date])))

在我当前的手动解决方案中，虽然全年不能很好地与目标线配合使用，但我不确定我是否做对了。

更新

所以进一步考虑这个。感觉我只需要能够创建一个表格，其中包含日期 - 每日目标 - 销售人员。基于月度目标，但不确定如何在 power bi 中做到这一点。理想情况下，您可以只添加/删除销售人员，然后重新生成该特定表。

Answer 1

我有两个解决方案。一种使用 DAX，一种使用查询编辑器。

---

DAX 解决方案:

1.创建一个日历表，其中包含您需要的所有日期。

如果 Targets 是包含您每月目标的表格，请使用如下公式创建一个新表格:

Calendar = CALENDAR(EOMONTH(MIN(Targets[Date]),-1)+1,EOMONTH(MAX(Targets[Date]),0))

2. 创建一个新表 DailyTargets 作为日期和销售人员的交叉联接。

CROSSJOIN 函数为每个日期和销售人员组合创建一行:

DailyTargets = CROSSJOIN(VALUES('Calendar'[Date]),VALUES(Targets[Salesperson]))

3.为您的每日目标创建计算列。

我通过查找每月目标并除以该月的天数来做到这一点:

DailyTarget = DIVIDE(
    LOOKUPVALUE(Targets[MonthlyTarget],
                Targets[Month], FORMAT(DailyTargets[Date],"mmmm"),
                Targets[Salesperson], DailyTargets[Salesperson]),
    DAY(EOMONTH(DailyTargets[Date],0)))

现在您有每个日期和每个销售人员的每日目标。

---

PowerQuery 解决方案:

1.创建一个日历表，其中包含您需要的所有日期。

创建一个空白查询并使用以下代码:

= List.Dates(List.Min(Targets[Date]),
             Duration.Days(Date.EndOfMonth(List.Max(Targets[Date]))
             - List.Min(Targets[Date])) + 1,
             #duration(1,0,0,0))

2.将此列表转换为表格。

单击“转换”选项卡下的“到表”并将该列从“Column1”重命名为“日期”。

3. 为月份名称创建自定义列。

您可以为此使用公式Date.MonthName([Date])。

4. 将此查询与 Targets 表合并(加入 Month 列)。

5.合并后，展开Salesperson和MonthlyTarget列。

6.通过将每月目标除以该月的天数来创建每日目标。

为此，您可以使用公式 [MonthlyTarget]/Date.DaysInMonth([Date])。

整个查询应该是这样的:

let
    Source = List.Dates(List.Min(Targets[Date]), Duration.Days(Date.EndOfMonth(List.Max(Targets[Date])) - List.Min(Targets[Date])) + 1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Month", each Date.MonthName([Date])),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom",{"Month"},Targets,{"Month"},"Targets",JoinKind.LeftOuter),
    #"Expanded Targets" = Table.ExpandTableColumn(#"Merged Queries", "Targets", {"Salesperson", "MonthlyTarget"}, {"Salesperson", "MonthlyTarget"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Targets", "DailyTarget", each [MonthlyTarget]/Date.DaysInMonth([Date]))
in
    #"Added Custom1"

您可以根据需要将其粘贴到高级编辑器中，而不是一步一步来。 (只要确保您使用的是您拥有的任何表名，而不是 Targets。)