Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: visual total not matching sum of individual ro...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

kriscoupe

Resolver IV

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2020
12:32 PM

Hi @Andvil

Just with this measure...

sumx(Values(Table[Month Year]),distinctcount(Table[Code]))

Try to alter it by wrapping a CALCULATE around the DISTINCTCOUNT to ensure context transition in initiated so

sumx(Values(Table[Month Year]),calculate(distinctcount(Table[Code])))

Your numbers being high suggests to me this is the issue.

Let me know if it helps.

Kris

Highlighted
##

**Did I answer your question? Mark my post as a solution!**

**Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!**

edhans

Super User VI

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2020
01:39 PM

@Andvil it is a small tweak. CHange ALL to ALLSELECTED.

```
code count =
IF(
HASONEVALUE( '2020'[MES] ),
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] ),
COUNTROWS(
ALLSELECTED(
'2020'[MES],
'2020'[NUMERO DE CARGA FINAL]
)
)
)
```

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
##

**Did I answer your question? Mark my post as a solution!**

**Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!**

edhans

Super User VI

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2020
01:52 PM

Also @Andvil , regarding this:

Also, in my original report i use the "Month Name Long" column under the Dates table. When using your formula I received an error message saying that formulas with ALL should include data from the same table, and I was using data from table 2020 and table Dates. If you can help me to keep using the Month Name Long data to filter would be great, otherwise I can change my report to use the month number column that is in table 2020.

That is going to require a bit more work as your model needs to use only fields form the date table for date fields, and you used the [MES] column originally, which is what I coded against.

That turns this a bit more into a consulting project vs a "how do I get this total calculation to work."

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
##

Andvil

Helper V

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2020
06:30 PM

Hello @kriscoupe,

Thank you very much for your help!

I found this formula on internet and I used it, and it worked:

CODE count = SUMX(SUMMARIZE('2020','Dates Table'[MonthNameLong]),CALCULATE(DISTINCTCOUNT('2020'[Code])))

I also tried with the formula you suggest and it worked:

sumx(Values(Table[Month Year]),calculate(distinctcount(Table[Code])))

Can you explain me please what is the difference between both formulas?

Best,

JALV

Highlighted
##

**Can you explain me please why the three formulas work and what are the differences between the three?**

Andvil

Helper V

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2020
06:44 PM

Hello @edhans,

I appreciate your willingness to help me.

1) I found this formula on internet and I used it, and it worked:

CODE count = SUMX(SUMMARIZE('2020','Dates Table'[MonthNameLong]),CALCULATE(DISTINCTCOUNT('2020'[Code])))

2) I tried the formula you suggest and it works (when using the month from the same table but not from the calendar table):

code count = IF( HASONEVALUE( '2020'[MES] ), DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] ), COUNTROWS( ALLSELECTED( '2020'[MES], '2020'[NUMERO DE CARGA FINAL] ) ) )

I also tried with the formula @kriscoupe suggests and it worked:

sumx(Values(Table[Month Year]),calculate(distinctcount(Table[Code])))

Best,

jalv

Highlighted
##

Andvil

Helper V

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2020
06:49 PM

Hi @edhans,

Thank you, as mentioned in the previous answer, these two formulas worked with the months from the calendar table and with the months from the same table:

1) CODE count = SUMX(SUMMARIZE('2020','Dates Table'[MonthNameLong]),CALCULATE(DISTINCTCOUNT('2020'[Code])))

2)

sumx(Values(Table[Month Year]),calculate(distinctcount(Table[Code])))

I am very grateful with your help, specially for helping me realize that there are some codes that are being repeated in more than one month.

Best,

JALV

Highlighted
##

kriscoupe

Resolver IV

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-27-2020
04:24 AM

Hi @Andvil,

The reason wrapping the CALCULATE around the DISTINCTCOUNT works is because in the SUMX you are in the row context. You need to wrap the CALCULATE to initiate context transition. The best way to see this in action is try writing a SUM(table[somecolumn]) in a calculated column. You'll notice you get the sum of the entire column in every row. Now try wrapping a CALCULATE around this SUM formula. You'll now see that you get a different number. This will be equal to the value in the table[somecolumn] if all the rows of the table are unique. If you have duplicate rows though it will be the sum of those duplicate rows. Effectively the CALCULATE changes that row into a filter context where the value of each column in that row is a filter. This article is a good overview.

Hopefully I answered your question. If so, could you mark as solution so that it comes to the top of the pile 😉.

Thanks!

Kris

Highlighted

**Did I answer your question? Mark my post as a solution!**

**Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!**

edhans

Super User VI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-28-2020
05:21 PM

Hi @Andvil ,

As for the difference of the formulas:

1) The SUMMARIZE Formula:

```
CODE count =
SUMX(
SUMMARIZE(
'2020',
'Dates Table'[MonthNameLong]
),
CALCULATE(
DISTINCTCOUNT( '2020'[Code] )
)
)
```

You should * never *use this type of formula. The reason is SUMMARIZE function is buggy when it comes to adding columns. You can see here that the Name and Expression part of the function is not recommended and has been depreciated. Instead, you can use SUMMARIZE to group the columns, but use ADDCOLUMNS around it to add the columns, so the right way to use it is as follows:

```
Summarize Measure =
VAR Result =
SUMX(
ADDCOLUMNS(
SUMMARIZE(
'2020',
'Dates Table'[MonthNameLong]
),
"Record Count", CALCULATE(
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] )
)
),
[Record Count]
)
RETURN
Result
```

You could do it without the VAR/RETURN construct. I always have that when building in DAX Studio. You can read more about properly using ADDCOLUMNS with SUMMARIZE here. You almost always have to wrap your expression for the ADDCOLUMNS function in CALCULATE, or refer to another measure, which puts in implicit CALCULATE around it, which causes a context transition. I try to avoid this as it can be expensive in terms of performance, but often it is the only or best solution. I personally am not a fan of ADDCOLUMNS/SUMMARIZE just because the DAX code is hideous to look at. 😁 Even DaxFormatter.com doesn't do a good job of formatting the code for me.

NOTE: There is a newer function called SUMMARIZECOLUMNS() but it should only be used for DAX queries or Calculated Tables, not measures. It doesn't handle context transitions at all. It won't return the wrong result - it will actually throw up a dialog box with an error that you cannot use it in the visual. You can use it for cards, but not much else. It is what I used though in DAX Studio to find out you had the same order number in multiple months. Great for queries. Not for measures.

2) My IF statement with the HASVALUES() measure is this:

```
If HasValues =
IF(
HASONEVALUE( '2020'[MES] ),
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] ),
COUNTROWS(
ALLSELECTED(
'2020'[MES],
'2020'[NUMERO DE CARGA FINAL]
)
)
)
```

It looks for how many values there are in the [MES] column. If there is more than one, it has to switch the method it uses, otherwise it uses a simple DistinctCount. I also generall y try to avoid the IF() function, but it does come in handy when working with the HASONEVALUE() and related functions, like ISFILTERED, ISINSCOPE, etc.

3) The final SUMX measure I like the elgance of:

```
SumX Measure =
SUMX(
VALUES( 'Dates Table'[YearMonthnumber] ),
CALCULATE(
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] )
)
)
```

It uses data lineage based on the YearMonth column of your date table, which allows for orders to be duplicated across months/years, then it does a DISTINCTCOUNT, but it only works with a CALCULATE around it to get down to the row context. Again, I try to avoid CALCULATE when I can, but it absolutely has its uses, and is used well in this measure.

4) I redid my HasValues with a HasValue 2 measure. This one would require you to change the column you use, and * you should do this anyway.* I used the MonthNameLong field, but you need to add a Month Number column. You should

```
If HasValues 2 =
IF(
HASONEVALUE( 'Dates Table'[MonthNameLong] ),
DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] ),
COUNTROWS(
ALLSELECTED(
'2020'[MES],
'2020'[NUMERO DE CARGA FINAL]
)
)
)
```

So which is better? {shrug} I ran some performance tests. The DAX query times are negligable in all cases. The times below are in milliseconds, and rerunning the queries all return sub-100 millisecond times, which means they all work as fast as you can blink. So go with the one that you are most comfortable understanding! With performance times like this, if your report is slow, it has nothing to do with this particular measure, no matter which you pick.

One final thing: In your model, you need to turn off automatic date/time intelligence in options, then mark your date table as a date table by right-clicking on it and marking as a date table. Performance improvement is negligable (most DAX queries took 50%-75% of the time to run after that) but going from 20ms to 15ms isn't going to help you much. But it is good practice to have a proper date table. It also gets rid of the 8-9 hidden date tables Power BI creates behind the scenes when you have automatic date/time intelligence turned on.

And one absolutely final note: Please mark one or more of these as the solution so we can know this is resolved and others will know the solution that worked for you.

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
##

Andvil

Helper V

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020
12:48 PM

Hi @edhans,

Thank you for your answer and explanation, it is one of the best and most complete answers I have received in my posts. I have marked it as the solution, thank you very much for helping me in this.

Also, thank you for going further and suggesting me to turn off automatic date/time intelligence and mark my date table as a date table, I have followed your suggestion.

I appreciate all your answers in this post.

Best,

JALV

Highlighted
##

Andvil

Helper V

Re: visual total not matching sum of individual row

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2020
12:51 PM

Hi @kriscoupe,

Thank you for your help, I have read the article and it is indeed a good article. I also appreciate all your answers that have helped me to solve this issue.

Best,

JALV