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