cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver IV
Resolver IV

Re: visual total not matching sum of individual row

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
Super User VI
Super User VI

Re: visual total not matching sum of individual row

@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]
        )
    )
)

edhans_0-1593203935565.png

 



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!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User VI
Super User VI

Re: visual total not matching sum of individual row

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."



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!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Helper V
Helper V

Re: visual total not matching sum of individual row

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
Helper V
Helper V

Re: visual total not matching sum of individual row

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])))
 
Can you explain me please why the three formulas work and what are the differences between the three?
 
Best,
jalv
 
Highlighted
Helper V
Helper V

Re: visual total not matching sum of individual row

 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
Resolver IV
Resolver IV

Re: visual total not matching sum of individual row

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.

 

https://www.sqlbi.com/articles/understanding-context-transition/#:~:text=The%20context%20transition%...

 

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
Super User VI
Super User VI

Re: visual total not matching sum of individual row

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 never use fields from your FACT table as a DIM field. You should always use your DIM table, or your date table for this. That is what they are for. I have many model shere I have hidden every single field in my FACT table and only use DIM tables and measures. I cannot always to this, but it is because I need to use some files only in my fact table, like Invoice numbers for a detailed report. Anyway, the updated measure is:

 

 

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.

 

edhans_1-1593388810637.png

 

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.

edhans_2-1593389786757.png

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.

 

 



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!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Helper V
Helper V

Re: visual total not matching sum of individual row

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
Helper V
Helper V

Re: visual total not matching sum of individual row

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

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors