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

问题描述

我对第3个月滚动平均值的度量有问题，无法在图表上正确地可视化它。

数据模型在这里：

但是当我将其绘制在柱形图中时加上产品A累计，我得到的值是错误的，这是产品不累计/ 3的3个连续值之和的产品不累计/ 3的值。

我应该在DAX中进行哪些更改以使其正确可视化？请帮助

解决方案

在这两种格式上，您似乎都遇到了许多Power BI陷阱源数据中的日期 和在可视化中选择显示日期列的方式。但是我想我已经解决了。这是我的结果：

验证一些数字：

 （4043 + 20 + 158）/ 3 = 1469 
 
（189+ 200 + 207）/ 3 = 199 
  

---

在这里详细信息：

我使用了此数据集，在其中略微更改了名称，以使其更易于编写DAX表达式，并使用 Get将其导入数据

 日期unAcc ACc 
 01-10-2017 00:00 4043 4043 
 01-11-2017 00:00 205 4248 
 2017年12月1日00:00 158 4406 
 2018年1月1日00:00 142 4548 
 2018年1月2日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 
  

由于某些原因我不知道，我遇到了与 Date 列相同的问题。但是，遵循 Date2 像这样：

  Date2 = 
 DATE（'Table1'[Date]。[Year];'Table1'[Date]。[MonthNo]; 1）
  

然后，我使用

  Moving_Average_3_Months = 
计算（
 AVERAGEX（'Table1';'Table1'[unAcc]）; 
 DATESINPERIOD（
'Table1 '[Date2]; 
 LASTDATE（'Table1'[Date2]）; 
 -3; 
 MONTH 
）
）
  

现在，如果您插入柱形图并将 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屋！