Forum Discussion
Incorrect report table column total
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
9 Replies
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:
- A thorough description of the problem
- Enough to understand the logic involved and the business problem you are solving
- Enough to understand where the result is falling short of expectations
- 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
- 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).
- Your model structure.
- At least a description of the facts and dimensions involved, with relevant fields listed, and relationship keys called out.
- 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).
- 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.
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
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.
- A thorough description of the problem
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.
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.
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
