Power BI 滚动平均 DAX 在柱形图上正确绘制 [英] Power BI Rolling Average DAX to plot correctly on Column Chart

问题描述

我对第 3 个月滚动平均值的测量有疑问，无法在图表上正确显示它.

数据模型在这里:

但是当我将它与 Product A Accumulated 一起绘制在柱形图中时，我得到了错误的值，即 Product unaccum/3 的值是 Product unaccum/3 的 3 个连续值的总和.

我应该在 DAX 中进行哪些更改才能使其正确显示?请帮忙

解决方案

当涉及到源数据中的日期格式时，您似乎在这里遇到了很多 Power BI陷阱"和您选择在可视化中显示日期列的方式.但我想我已经想通了.这是我的结果:

只是为了验证一些数字:

(4043 + 20 + 158)/3 = 1469(189+ 200 + 207)/3 = 199

<小时>

以下是详细信息:

我使用了这个数据集，我稍微更改了名称，以便更轻松地编写 DAX 表达式并使用 Get Data

导入它

日期 unAcc ACc01-10-2017 00:00 4043 404301-11-2017 00:00 205 424801-12-2017 00:00 158 440601-01-2018 00:00 142 454801-02-2018 00:00 312 486001-03-2018 00:00 258 511801-04-2018 00:00 176 529401-05-2018 00:00 210 550401-06-2018 00:00 189 569301-07-2018 00:00 200 589301-08-2018 00:00 207 6100

由于我仍然不知道的原因，我遇到了与 Date 列相同的问题.但遵循 Date2 像这样:

日期2 =DATE('Table1'[Date].[Year];'Table1'[Date].[MonthNo];1)

然后我使用

Moving_Average_3_Months =计算 (AVERAGEX ('Table1';'Table1'[unAcc]);日期期间 ('表1'[日期2]；LASTDATE ('Table1'[Date2]);-3;月))

现在，如果您插入 柱形图 并将 Date2 分配给 Axis 和 Moving_Average_3_months 以及 unAcc 到 Values，你会得到这个:

这不是我们想要的.因此，转到可视化设置并将 Date2 从 Date Hierarchy 更改为简单的 Date2，如下所示:

就是这样:

这是一个表格，所以你可以看到数字是正确的:

就你而言，也许你唯一需要做的就是最后一部分.

请不要犹豫，告诉我它对你的效果！

I have a problem with the measure of the 3mth rolling average to visualise it correctly on the graph.

The data model is here:

https://docs.google.com/spreadsheets/d/1naChcuZtjSbk0pVEi1xKuTZhSY7Rpabc0OCbmxowQME/edit?usp=sharing

I am using the formula below to calculate 3mth average through a measure:

Product3Mth = CALCULATE(SUM('Table'[Product A uncum]);DATESINPERIOD('Table'[Date];LASTDATE('Table'[Date]);-3;MONTH))/3

When I am plotting it as a table it is showing right values for each month.

But When I am plotting it in the column chart together with Product A Accumulated I am getting wrong value which is the value for Product unaccum /3 insted of sum of 3 consecutive values for Product unaccum /3.

What should I change in the DAX to have it visualised correctly? Please HELP

解决方案

It seems you've stumble accross quite a few Power BI "gotchas" here when it comes to both the format of the date in your source data and the way you've chosen to display the Date column in your visulaization. But I think I've figured it out. This is my result:

And just to verify some numbers:

(4043 + 20 + 158) / 3 = 1469

(189+ 200 + 207) / 3 = 199

---

And here are the details:

I used this dataset where I've changed the names slightly to make it easier to write DAX expressions and imported it using Get Data

Date                unAcc   ACc
01-10-2017 00:00    4043    4043
01-11-2017 00:00     205    4248
01-12-2017 00:00     158    4406
01-01-2018 00:00     142    4548
01-02-2018 00:00     312    4860
01-03-2018 00:00     258    5118
01-04-2018 00:00     176    5294
01-05-2018 00:00     210    5504
01-06-2018 00:00     189    5693
01-07-2018 00:00     200    5893
01-08-2018 00:00     207    6100

And for reasons still uknown to me, I had the same issues as you had with the Date column. But following some tips from the Date2 like this :

Date2 = 
DATE('Table1'[Date].[Year];'Table1'[Date].[MonthNo];1)

Then I calculated the three month average using a

Moving_Average_3_Months = 
CALCULATE (
    AVERAGEX ( 'Table1'; 'Table1'[unAcc] );
    DATESINPERIOD (
        'Table1'[Date2];
        LASTDATE ( 'Table1'[Date2]);
        -3;
        MONTH
    )
)

Now, if you insert a column chart and assign Date2 to the Axis and Moving_Average_3_months together with unAcc to Values, you'll get this:

And that's not what we want. So go to the Visualization settings and change Date2 from Date Hierarchy to simply Date2 like this:

And that's it:

And here's the whole thing as a table so you can see that the numbers are correct:

In your case, maybe the only thing you have to do is that very last part.

Please don't hesitate to let me know how it works out for you!

这篇关于Power BI 滚动平均 DAX 在柱形图上正确绘制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！