在 Power BI 的折线图中显示 0 值以查找缺失数据 [英] Show 0 value on Line Chart in Power BI for missing data
问题描述
我有一个包含稀疏值的表格，但在我的折线图中，当没有数据可显示时，我希望这条线回到零.例如:
I have a table with sparse values, but in my line chart I'd like the line to go back to zero when there's no data to show. e.g:
代替:
我想要:
为了生成第一个图表，我使用了一个带有一系列 x 轴值的生成表，并将稀疏数据表连接到该生成表中以提供值.
To produce the first graph I used a generated table with a series of values for the x-axis, and the sparse data table was joined onto that generated table to provide the values.
在某些情况下，会有值的总和，显示在零上方的行.显示在轴下方的值实际上只是我数据中非常小的值，它们不是零.
In some cases there's a summation of the values, which show with the line above zero. The values that appear low down to the axis are in fact just very small values from my data, they are not zeros.
在轴上选择显示没有数据的项目"似乎并没有达到我想要的效果，这是我发现的其他答案所暗示的.我有什么遗漏吗?
Selecting "Show items with no data" on the axis doesn't seem to do what I want, which other answers I've found are suggesting. Is there something I'm missing?
我想知道我需要使用哪些选项，或者我应该实施的数据表是否有某种技巧，这将使我在折线图(如第二张图所示)上出现零没有找到数据.
I would like to find out what options I need to use, or if there's some kind of trick with the data table I should implement, which would give me zeros on the line graph (as shown in the second image) when there is no data found.
推荐答案
Ryan 为在 Power Query 编辑器中处理此问题提供了很好的答案.如果您(或有相同问题的人)无法编辑查询，这里有几个选项可用于在 DAX 和视觉设置中解决此问题.
Ryan provides a great answer for handling this in the Power Query editor. If you (or someone with your same question) can't edit the queries, here is a couple of options for solving this issue in DAX and visual settings.
关于 Ryan 的回答，我要注意的一点是null".和零"是截然不同的项目.空"表示没有数据和零"；表示 0.根据您查看的数据类型，将它们视为相同可能会导致错误信息.
One thing I want to note about Ryan's answer is "null" and "zero" are drastically different items. A "null" means no data and a "zero" means 0. Depending on what kind of data you're looking at, treating those as the same could lead to misinformation.
解决方案:
如果您的 X 轴是分类"，您可以使用显示没有数据的项目".此时，X 轴上没有数据的任何值都将显示为线条中的间隙.
If your X axis is "Categorical", you can use the "Show items with no data". At which point any value on your X axis that has no data will be shown as a gap in the line.
这将是我显示没有数据的项目的首选方法.
This would be my preferred method to show items with no data.
- 如果您的 X 轴是连续的"；和/或您真的想显示零，只需使用以下公式创建一个度量，将所有空白变为零.
这是折线图在正常度量下的样子.
This is what the line chart would look like with a normal measure.
这就是使用新措施时的样子.
And this is what it looks like using the new measure.
这是给出最后一张图片中行为的度量公式.
And here is the measure formula that gives the behavior in the last picture.
Total Amount = IF(ISBLANK(SUM(Table1[Amount])), 0, SUM(Table1[Amount]))
这篇关于在 Power BI 的折线图中显示 0 值以查找缺失数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
