Power Bi DAX：除数值并设置为每周 [英] Power Bi DAX: Divide Value and set it for each week

问题描述

我将一月份的目标值设置为20，但将本月的目标值设置为20，我需要每周显示一次。如果一个月中有4个星期，则必须将目标除以4；如果一个月中有5个星期，则必须除以5。就是这么简单，我正在使用折线图和聚簇柱形图来显示我的数据，我需要将目标分散到每月的每个星期。我还需要另一个字段来执行此操作，但请确保我可以复制您的公式并使其适用。

I have a target value of 20 for January but it is 20 for the month, i need to show this over each week. I have to divide the target by 4 if there are 4 weeks in a month and by 5 if there are 5 weeks in a month. It is as simple as that, i am using a line and clustered column chart to display my data, i need the target spread out into each week of the month. I also need another field to do this but im sure i can replicate your formula and make it applicable.

我添加了WeeksinMonth列，该列计算特定月份的周数，例如1月为5周，2月为4周。我需要一个IF语句，将目标和价值除以一个月中的几周。例如，如果一个月有5周，则将目标和值除以5，如果一个月有4周，将目标和值除以4。

I have added a WeeksinMonth column that counts how many weeks are in a particular month e.g January has 5 weeks and February has 4 weeks. I need an IF statement that will divide the target and value by how many weeks in a month. e.g if month has 5 weeks then divide target and value by 5, if month has 4 weeks divide target and value by 4.

我有一个日历表，其中包含周值我可以用来将分割后的目标以及我在excel中获得的期望输出（请参见附件）。

I have a calendar table with week values which i can used to put the divided target into and also the desired output i made in excel (See attached).

我将如何处理？

所需的输出

日历表

推荐答案

在此处输入代码您可以在日历表中创建一个额外的列：

enter code hereYou can create an extra column in your calendar table:

WeekMax = 
var stOfMonth = Weeks[Start of Week].[Month]
var stOfYear = Weeks[Start of Week].[year]
return CALCULATE(max(Weeks[Weekinmonth]);FILTER(Weeks;Weeks[Start of Week].[Month] = stOfMonth && Weeks[Start of Week].[Year] = stOfYear))

麦确保您的模型中的日历和目标日期之间存在关系。现在，您可以使用该数字来表示该周/日期。

Make sure you have a relation in your model between calendar and target date. Now you can use this number for that week/date to divide by.

您现在可以在目标表中添加额外的列：

You can now add an extra column in your target table:

WeeklyTarget = Targets[Target]/Related(Calendar[WeekMax])

这篇关于Power Bi DAX：除数值并设置为每周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！