DAX: Create new table by SUMMARIZE with ROLLUP returns duplicate rows

[Anonymous]

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

[v-robertq-msft · Accepted solution]

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.

[Anonymous]

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