Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Trying to create a summary table

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...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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