Forum Discussion
DAX: Create new table by SUMMARIZE with ROLLUP returns duplicate rows
Hello,
I am summarizing budget and actual variance data for a backward looking financial table.
The raw data for each month has a number of accounts.
I want to summarize out the accounts and just get sums for the month.
But what's happening is I get the summary, then the data is repeated again in another set of rows but without a date column data. Here is some sample data showing the problem:
I'm still a bit new to DAX and modelling, with only a few weeks concentrated experience so I'm sure there's something simple I'm missing. Looking forward to see what the more knowledgeable people here might advise.
- Jim
Hi, Anonymous
According to your description, I think that you only have to group by month in the summarize() function, you can try this measure:
Over variance CE By Month = SUMMARIZE(‘Overhead Variance CE’ , ROLLUP(‘WWI Fiscal Calendar’ [Month]), “Actual by Month”, SUM(‘Overhead Variance CE’ [Actual]) , “Budget by Month”, SUM(‘Overhead Variance CE’ [Budget]) , “Budget Variance by Month”, SUM(‘Overhead Variance CE’ [Budget Variance]) )And you can get what you want, like this(this table is created by data I imitated).
If not, you can reply to me and post some sample data(without sensitive data).
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- v-robertq-msft
Community Support
Hi, Anonymous
According to your description, I think that you only have to group by month in the summarize() function, you can try this measure:
Over variance CE By Month = SUMMARIZE(‘Overhead Variance CE’ , ROLLUP(‘WWI Fiscal Calendar’ [Month]), “Actual by Month”, SUM(‘Overhead Variance CE’ [Actual]) , “Budget by Month”, SUM(‘Overhead Variance CE’ [Budget]) , “Budget Variance by Month”, SUM(‘Overhead Variance CE’ [Budget Variance]) )And you can get what you want, like this(this table is created by data I imitated).
If not, you can reply to me and post some sample data(without sensitive data).
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Community Support Team _Robert Qin,
Thanks for your solution. Yes this solves the duplicate row issue.
One follow up question:
12 Months of data from 2019 10, to 2020 09 is shown simply as rows 1-12. What would I do to get the data in a date ordered list so that the table is not misleading?
Thanks so much again!
- v-robertq-msft
Hi, Anonymous
You can create a table chart, then place the fields you wanted into a table chart, and click on the heading of the field you want to sort, like this:
And you can get what you want.
Best Regards,
Community Support Team _Robert Qin