Incorrect report table column total

[timmcn]

Hi,

Firstly, my apologies as I am new to Power BI Desktop and I'm sure this is something obvious.

When I create a report which includes our Weighted Refill Score measure, the total is far greater than the sum of the values for each row. Furthermore, I can't find anywhere to check whether it is set to count, sum, average etc. (although I can't think of any calculation that would generate this total).

Here is a screenshot of the report - screenshot

How can I get the column to just be the sum of the values?

Best regards,

Tim

[jeffreykeryk]

Here is an example: Create a pivot, and set summarization to Average. In my case, the table averages are fine, but the totals are whack. Here is Excel, with correct values (average of 3 values is 1,916,498:

2014 Q1
1,046,283
1,981,491
2,721,719
1,916,498

However, Power BI returns 1,932,746.

Please advise, as this is a deal killer for me.

[greggyb]

@jeffreykeryk,it's usually better to start your own thread for problems like this. Everyone's model will be unique in some way, so the steps that timmcn might need to take to get his grand total to behave as is appropriate for his business logic are very likely different than those needed for yours.

Additionally, based on the information you've shared, there is no way to understand what the inputs to your measures are, or where the problem might lie. Please see my first response in this thread for some helpful tips to make sure you get responses that are relevant and useful to your specific problem.

[jeffreykeryk]

Hi Gregg,

Can you tell me how to add a post? I am struggling to use this forum.

Can I paste in a screenshot?

I appreciate your help.

Jeff Keryk

[Greg_Deckler]

@jeffreykeryk - To add a new post, from Home page, scroll down and click on the appropriate forum. Use the "New Message" button to start a new topic. You can upload pictures using the Photos button if using rich text. Note that Edge and IE sometimes have issues with this so I sometimes resort to Chrome to get this done. I generally take a screen shot, paste it into Paint, crop, save upload but you can do similar things with snippet tools, etc. But, you generally have to save it as a file and then upload it.

[greggyb]

Without understanding your data model or the measure in question it is impossible to provide helpful answers.

A good question (i.e. one that will get you useful responses quickly) should include the following:

1. A thorough description of the problem
   1. Enough to understand the logic involved and the business problem you are solving
   2. Enough to understand where the result is falling short of expectations
2. An enumeration of steps you have taken and attempts you've made to fix the problem (measure definitions, model alterations), and where these fall short
3. The full text (not a screenshot, but text using the code block functionality in these forums, so we can copy paste without introducing our own errors copying by hand from a screenshot) of all involved measures and column definitions (if any).
4. Your model structure.
   1. At least a description of the facts and dimensions involved, with relevant fields listed, and relationship keys called out.
   2. If your model is nontrivial, a screen capture of the model diagram will be quite helpful, as well as sample data if you are able to share any (again, don't take a screenshot - we don't want to transpose pages of data to be able to help you).
   3. Sharing on OneDrive (or similar) a sample .pbix with the minimum amount of data to recreate your problem is the most helpful to us in providing fast, accurate responses to you.

[timmcn]

Thanks greggb,

Point taken, but it was meant to be a more general question. I'm happy to do the detailed problem solving myself but was just hoping for a little guidance on how Power BI calculates column totals. Coming from Excel pivot tables where you could always expect the totals to be one of count, sum, average etc. of the row values, the Power BI report column totals look to work differently.

To rephrase:

1. is there a fundamental difference with how Power BI calculates column totals that could cause the total to be / look unrelated to the column values (as shown in the screenshot)

2. is there somewhere in the Report view / report properties where you can set it to do a simple sum calculation?

Best regards,

Tim

[Greg_Deckler]

@timmcn - The default summarization is Sum, this can be overridden in the data Modeling area. Also, you can set this on the visualization itself. Click on your Table visualization, in the Values area (chart icon, not paintbrush icon), click on the little down arrow for the column you want, in your case "Weighted Refill Score". There you can set it to the following summarizations:

• Don't summarize
• Sum
• Average
• Minimum
• Maximum
• Count (Distinct)
• Count
• Standard deviation
• Variance
• Median

A number of these summarizations will cause the "Total" to seemingly have zero to do with the numbers in the column. Sometimes this is because the Table rows are being summarized in some way and at other times, just the nature of the column summarization. Standard deviation and Variance can cause this for example.

So:

1. Yes

2. You can set this in the data Modeling tab, middle spreadsheet looking icon on the lefthand side in Desktop, click your table, click your column, choose Modeling tab. In the Properties area of the ribbon, change the Default Summarization. Also, when on the report tab, click your visualization, on the righthand side, click the column chart icon in the Visualizations area, in the Values, click the little down arrow on the data column and choose your summarization.

[timmcn]

Thanks @Greg_Deckler, much appreciated.

I suspect my issue has something to do with a) it being a measure and not a calculated column (the summarizations drop down in the Values area only seems to appear in for columns) and b) context... I read somewhere measures do not have row context.

Will read up more, try a few things and post back here with solution.

Best regards,

Tim

[greggyb]

@timmcn, there is no row context in a table visualization. Row context only exists in an iterator function (FILTER(), *X()) or in an added column in a table in the data model. The table and matrix visualiations create filter context for each label that exists in them.

Since this is a defined measure, that's why I asked for samples or description of your data model and sharing the measure definition. Without understanding that, we can't tell you what's going on that's different between a detail level and the grand total level in your visualization.