Forum Discussion
Avoiding SUM blanks creates date problem on chart
Hi all,
I'm having some challenges with blank values when summing costs of a fact table. Since there are date values where costs are zero for certain dates in the fact table.
As showing in the first image. The date axis shows only the dates I want (last 30). But there are blank values in the matrix below.
In the second image I've added + 0 (Costs = SUM( 'fact Costs'[PreTaxCost]) + 0 ) to my Cost measure to avoid these blanks. But then my daye axis shows more dates than I wish.
Can anyone explain why this behaviour occurs, and how to resolve it?
5 Replies
To me, the behavior seems fine. When you have blank values at the start, the chart is ignoring the blanks or you will not get data for those months to you only see non-blank datadata.
When you add +0, you get data for the blank row. This also a kind workaround to get outer join. So now you get 0 row data, which can be shown in the chart/visual so in displays
What is objective of adding 0
The objective for adding +0 is avoiding blank in my matrix. When the costs are blank, in my opinion it should show as 0.
I don't understand why my chart shows more dates than set in the page level filter. In my page filter I request for the last 30 days, which breaks when adding +0 to my Cost SUM calculation.Hi Anonymous ,
Could you please provide some sample data in table "fact Costs" and the fields or measures be applied on below chart in order to make troubleshooting? Thank you.
In addition, please try to update the formula of measure"Costs" as below with function COALESCE, then check if that chart still display more dates than you want...
Costs = COALESCE(SUM( 'fact Costs'[PreTaxCost]),0)Best Regards
Rena
