Forum Discussion
Trying to create a summary table
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
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
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
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
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