Forum Discussion
Incorrect report table column total
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.
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
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.
