Forum Discussion
creating a summary table
Hi cobus_19,
You can use summarize function(DAX) to achieve your requirement.
Table = SUMMARIZE(Sheet1,Sheet1[origin date],"State1",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 1 date]),"State2",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 2 date]),"State3",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 3 date]))
If above is not help, please provide some sample data.
Regards,
Xiaoxin Sheng
This was perfect! Thank you very much!
If I may introduce a further complexity:
This gives me a great daily summary, however how would I set about including some other columns from my main table?
For example, what if I want to see a breakdown of [category] per day. How do I introduce this without breaking the daily counts?
So my current table output is
Origin Date | Status 1 Count | Status 2 Count | etc
date1 | 10 | 20
date2 | 5| 30
etc
My desired output is:
Origin Date | Category | Status 1 Count | Status 2 Count
date1 | category1 | 5 | 15
date1 | category2 | 5 | 5
date2 | category1 | 1 | 10
date2 | category2 | 2 | 10
date2 | category3 | 3 | 10
Your help is much appreciated.
Regards
Cobus
Hi cobus_19,
Yes, you can add other columns from main table.
Table = SUMMARIZE(Sheet1,Sheet1[origin date], //add these columns here
Reference:
Regards,
Xiaoxin Sheng