Left outer join sum with DAX

1
votes

I have 2 tables:

  1. Fact sales table (sales)
  2. Dimensional period table (dimperiod)

enter image description here

I have joined them through my model on the Period column. However, when I display the sales sum I got this.

enter image description here

I would like to have a row where no sales were made with a sum equal to 0. As below:

enter image description here

I've used the following DAX but didn't work.

CustomSales:=CALCULATE(SUM([Sales]), NATURALLEFTOUTERJOIN(sales,dimperiod))
1
excelpowerpivotdaxpowerbi

1 Answers

5
votes

Just change the pivot table to display blanks.

Or add 0 to your measure. Pivot tables do not display blanks by default. The first option above forces the pivot to display blanks, but you'd need to set this per pivot table. Addition implicitly coerces a blank to a numeric type.

SumSales:=
SUM( 'Sales'[Sales] ) + 0