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

问题描述

我正在研究 Power BI 中的损失三角形，其中 AccidentYear 是行，DevYear 是列.

表中的值是由度量创建的Running Total:

运行总损失=计算(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])返回计算(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])返回如果(年(一个月(日期(当前年份 - 1、12、31)，CurrDevYear)) >最大年，空白的()，计算(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屋！