Default summarization method will not match actual summarization

[laurachung]

Hi all!

So I have this one column which exists across a few tables (context below), which has default Summarization set to Sum (which I want), but every time I put it into a report, it takes the Average instead.

For a bit of background, I have several tables which have different data for the same projects. I've transformed these tables largely in the same way (among other things, transposing rows to columns, then promoting headers). After doing this, I've gone back into Excel and added two rows into my tables (which have then been transformed into columns after being pulled into Power BI). All of the data is pulling correctly for these new entries but I'm only getting this problem with the new columns, which leads me to believe that what I've done might be linked?

Any ideas on what the issue/solution might be? 

Thanks

 

 

 

[laurachung]

Hi @v-yangliu-msft 

Thanks for the reply and apologies for the delay. The best I can do for you is to provide further detail in the form of screenshots. I hope this is enough.

To answer your questions first:

• State-Wide Telephony is the column where the data is correct but the default Summarization is incorrect.
• I just want State-Wide Telephony to Sum when I put it in a visualization, but for some reason it defaults to taking the Average.
• The two rows were added after the Power Query was originally performed.

1: The original data - you can see months as headers and the project names are in the first column

 

2: I used the following transformations to transpose rows into columns and make the project names headers instead. The type changes were just from text to decimal numbers.

3: This is how the table displays in Data. The two columns that were added later are now on the end, after the index, and this is different to how it displays in Power Query. You can also see that the Summarization is set to Sum

4: However, when I drop State-Wide Telephony into a graph, it automatically gives me the Average rather than the Sum. Below is a stacked bar chart which I havent edited outside of selecting the fields. I don't have a problem with any other columns, not even with the second new column. 

I hope this helps to clarify things.

Laura

[v-yangliu-msft]

Hi  @laurachung  ,

What is the original data, what operations were performed in the power query, and whether the two rows were added before or after the power query was performed.

Is the column State-Wide Telephony where the data is incorrect? What does the actual effect need?

can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.