如何在运行总计矩阵Power BI中填写空白 [英] How to fill out blanks in Running Total matrix Power BI

问题描述

我正在Power BI的Loss Triangle中工作，其中 AccidentYear 是行，而 DevYear 是列。

表中的值是运行总额通过度量创建的：

 运行总损失= 
 CALCULATE（
 SUM（fact_Losses [PaymentAmount]），
 FILTER（ALL（fact_Losses [DevYear]）， fact_Losses [DevYear]< = MAX（fact_Losses [DevYear]））
）
  

链接获取原始数据：

对于2012年开发年而言， 36.
但是我仍然需要显示最后一个数字，在其余的空白单元格中为 172,888.65 。

我尝试使用 ISBLANK（），但到目前为止没有成功。

理想的结果应如下所示：

更新：
pbix文件可在此处找到：

该度量不评估

---

@Saaru的建议是一个非常简单的解决方法，但这是另一种选择

创建一个独立的表以用于矩阵列：

  DevYears = VALUES（fact_Losses [DevYear]）
  

如果将其用于矩阵列并更改

  RunningTotalLoss = 
 VAR CurrDevYear = MAX（'DevYears'[DevYear]）
返回
 CALCULATE（
 SUM（fact_Losses [PaymentAmount]），
 FILTER（ALL（fact_Losses [DevYear]），fact_Losses [DevYear]< = CurrDevYear）
）
  

然后您将看到一张完整的桌子。

这不是您所想的上三角，但是您可以使用 IF 添加该逻辑：

  RunningTotalLoss = 
 VAR CurrDevYear = MAX（DevYears [DevYear]）
 VAR MaxYear = CALCULATE（MAX（ fact_Losses [AccidentYear]），ALLSELECTED（fact_Losses））
 VAR CurentYear = MAX（fact_Losses [AccidentYear]）
 RETURN 
 IF（
 YEAR（
 EOMONTH（
 DATE（CurentYear-1，12，31），
 CurrDevYear 
）
）> MaxYear，
 BLANK（），
 CALCULATE（
 SUM（fact_Losses [PaymentAmount]），
 FILTER（ALL（fact_Losses [DevYear]），fact_Losses [DevYear]< = CurrDevYear ）
）
）
  

I am working on Loss Triangle in Power BI where AccidentYear are rows and DevYear are columns.

The values in the table are Running Total created by measure:

Running Total Loss = 
    CALCULATE( 
    SUM(fact_Losses[PaymentAmount]),
    FILTER(ALL(fact_Losses[DevYear]),fact_Losses[DevYear]<=MAX(fact_Losses[DevYear]))
    )

Link to get Raw data:

https://www.dropbox.com/s/dvb6cu1k4vmzkur/ClaimsLloysddd.xlsx?dl=0

Running Total Cumulative Data looks like this:

AccidentYear    DevYear Running Total Loss
2012            12          164714.11
2012            24          167727.65
2012            36          172888.65
2013            12          2314247.18
2013            24          4074094.91
2013            36          5247246.06
2013            48          5576930.29
2013            60          6487155.06
2013            72          6899512.68
2014            12          3367220.82
2014            24          4831946.69
2014            36          5741213.36
2014            48          6750204.17
2014            60          8384764.91
2015            12          7624575.21
2015            24          9935018.26
2015            36          11767207.67
2015            48          14653278.99
2016            12          8531229.05
2016            24          11768128.83
2016            36          17178123.28
2017            12          7390158.93
2017            24          12695778.03
2018            12          13136428.25

Then I am using matrix visual with AccidentYear are rows and DevYear are columns.

For Year 2012 Development Year only goes till 36. But I still need to display the last number, which is 172,888.65 in the rest of empty cells.

I tried to utilize ISBLANK() but did not have success so far.

The desirable result should look like this:

UPDATE: pbix file can be found here:

https://www.dropbox.com/s/wl1ot9ejgyv8yi3/Loss%20Triangle%20United%20Specialty.pbix?dl=0

解决方案

There's nothing you can do with the measure itself to get those to show up since no rows exist in the table that match both the AccidentYear and DevYear in those cells.

In fact, if you set your measure to a constant value of 1, then your matrix will look like this:

The measure will not evaluate in cells where there doesn't exist any data.

---

@Saaru's suggestion is a pretty simple workaround, but here's another option.

Create an independent table to use for your matrix columns:

DevYears = VALUES(fact_Losses[DevYear])

If you use that for your matrix columns and alter your measure a bit,

RunningTotalLoss = 
VAR CurrDevYear = MAX('DevYears'[DevYear])
RETURN
CALCULATE( 
    SUM(fact_Losses[PaymentAmount]),
    FILTER(ALL(fact_Losses[DevYear]), fact_Losses[DevYear] <= CurrDevYear)
)

then you'll have a full table.

This isn't upper triangular like yours is, but you can add that logic with an IF:

RunningTotalLoss = 
VAR CurrDevYear = MAX(DevYears[DevYear])
VAR MaxYear = CALCULATE(MAX(fact_Losses[AccidentYear]), ALLSELECTED(fact_Losses))
VAR CurentYear = MAX(fact_Losses[AccidentYear])
RETURN
IF(
    YEAR(
        EOMONTH(
            DATE(CurentYear - 1, 12, 31),
            CurrDevYear
        )
    ) > MaxYear,
    BLANK(),
    CALCULATE( 
        SUM(fact_Losses[PaymentAmount]),
        FILTER(ALL(fact_Losses[DevYear]), fact_Losses[DevYear] <= CurrDevYear)
    )
)

这篇关于如何在运行总计矩阵Power BI中填写空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！