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?
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:
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.
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?
@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:
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.
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.
Proud to be a Datanaut!
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.
@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.
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:
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.
@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.
Proud to be a Datanaut!