如何在运行总矩阵 Power BI 中填写空白

本文介绍了如何在运行总矩阵 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:

www.dropbox/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:

www.dropbox/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) ) )