Trying to create a summary table

[Anonymous]

Hi there, 

I am trying to do the following. 

I have a file containing sales data: 

Customer number, sales date, sales amount.

111111,03/04/2016,$300.00

111111,10/10/2016,$800.00

111111,03/06/2018,$300.00 

I want to create a summary table that would look like this.

Customer number, year of sale, sum(sales amount). 

111111,2016,$1100.00

111111,2018,$300.00

Up to now this is fine with the summarize dax command. 

However, 

- I want to make sure that a year without a sale, my customer shows as 0.

So I am missing the

111111,2017,$0. 

I was going to create two distinct table (one for customer and one for year) and make an left outer join from customer to year, then add a column with the sales summary (default to 0) but there might be a better solution.  

Thank you all for your help. 

Francois

[Anonymous]

1. First of all, you should have a proper calendar that's connected to your fact table. Please learn about a proper Date table in Power BI.

2. Then you should slice by the calendar's attributes (like months, for instance) and not by fields in the fact table. You should also have a Customer dimension connected to your fact table. The rule is that one shouldn't slice by anything that's directly embedded in the fact table, only by dimensions. There are many GOOD reasons for this.

3. You can get 0 instead of BLANK if you write your measure as:

[Sales]  =
sum( FactTable[Sales Amount] ) + 0

Best

Darek

[Anonymous]

Sorry for the long wait for the answer. 

yep, I know about the proper date table.  What I did is a cross join between my customer unique id in the customer table and the year in the data table. 

That would give me something like this. 

cust    year    

1000   2000

1000    2001

....

So I am sure that I have all my customer and year lined up. 

Second I created a new column that summed up the yearly sales for all customer. 

Now what I want to see is the following: 

Cust      Year    Yearly sales   Cumulative 

1000     2001       230.00           230.00

1000     2002           0.00           230.00

1000     2003       500.00           730.00

1000     2004          50.00          780.00 

I just found a formula derived from my yearly sales.  I just had to think about it. 

It looks like this 

Cumulative total = sum(salesfile[salesamt]), Filter(salesfile,salesfile[custid]=yearlysummary[custid]&&sales[salesdate].[year] <= yearlysummary[year]

We do not need granularity lower than the year in this case so this works fine for me. 

Thanks

Francois

[Anonymous]

I'm glad it works for you but... you should not (actually NEVER) put a full table fact table as a filter for a measure. This is because if you do that, then you are putting not only the full fact table in there but also the expanded version of the table and this can and will prove to be very expensive (think: slow) on a very big fact table. The golden rule of DAX is: Never filter a table if you can filter a column. That's not the only reason why you should not filter by a full table but to explain all the perils I'd have to write a whole chapter of a book. Don't have time to do this.

You've been warned.

Best

Darek

[Anonymous · Accepted solution]

Thank you Darek, 

Sorry for the late response.

Indeed it ended up being slow and memory intensive when updating the data. 

I have created measures and filters on column but I need to derived a column based from two measures. 

This is a different topic however this was the purpose of the summary table. 

Thanks

F