Wrong totals / Columns with "Don't summarize" show total

[mrupsidown]

I have a table built with a SQL Query (Query1) and several columns. I then created a new measure which sums the numbers of 4 other columns with a simple DAX formula. I want this column to also show the total but it is plain wrong.

Here is the formula :

Measure1 = MAX(Query1[col1]) + MAX(Query1[col2]) + MAX(Query1[col3]) + MAX(Query1[col4])

Another issue is that I have some columns, for instance the one labelled "YELLOW" below which are set to "Don't summarize" and... show the total. Why is that??

[v-jingzhang · Accepted solution]

Hi @mrupsidown 

Can you provide some sample data to help us understand your question more clearly? How to provide sample data in the Power BI Forum

For the 1st item, measures are evaluated according to the context. A measure may return different results when it's in different visuals or when you put different columns into the same visual. In both scenarios, the context may change. For example in below image, there are two measures calculating the total of 3 columns. You will see the difference between when they are in the same table visual and when they are in a single card visual.

Sum Measure 1 = SUM('Table'[Column1]) + SUM('Table'[Column2]) + SUM('Table'[Column3])

Sum Measure 2 = CALCULATE([Sum Measure 1],ALL('Table'))

For the 2nd item, if you drag a column into a table visual and don't want it to show the total value, you can select the table visual, click the down arrow on this column in Fields pane and select Don't summarize option. This will influence how the column behaves in this visual and hide the total. If you don't want a measure to display total, you will need to adapt the measure. 

Hope this helps.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[HotChilli]

1st item is a measure totals problem - lots of posts about these and check Greg Deckler's community blog about these.

2nd item.  Summarization ("Don't summarize") is a quality of the column.  That determines the default action when you drag the field into a visual.

  The total in the picture is a quality of the visualisation eg table or matrix.  So not the same thing.

--

See this for more help (the first technique is advanced - only use it if really necessary)

https://radacad.com/removing-the-total-value-for-a-column-in-the-table-visual-of-power-bi-using-isfi... 

[mrupsidown]

That didn't help. I have seen a few posts about wrong measure totals that all propose "solutions" that include utterly complicated calculations and formulas when I am just doing a simple SUM of 4 numbers. I can't think of why I'd need any of this... Just checked Greg Deckler blog and couldn't find anything related to that. Link?