visual total not matching sum of individual row

[Andvil]

Hello,

I am having a problem with the total of a column that contains a measure. I do a distinct count in sales code to see how many sales we had in each month. I sum the individual rows (Months) but they have a different total than the shown in the visual. I have tried in many ways but I still have differences. In other columns, the sum of the individual totals matches the total from the visual, so it only happens in the sales code column.

For exmple, when adding the 5 values I get a total of 82.210 and not 81.705.

I attach my example so you can help me figure out this difference. : Distinct Count Total error 

I would appreciate your help.

Best,

JALV

[amitchandak]

@Andvil , Power BI recalculate Grand total. So you need to force a row context to recalculate.

One of the way given in the example

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

[Andvil]

Hello @amitchandak,

I tried both measures but they are wrong.

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

This one gave me extremely high values, I do not know why.

sumx(summarize(Table[Month Year],"_1",distinctcount(Table[Code])),[_1])

This one gave me the value that I have and is incorrect because the sum of each month is not correct.

Best,

JALV

[kriscoupe]

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

[Andvil]

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

[edhans]

The reason is the total is not summing the values above it, but is redoing your measure of DistinctCount. So if your data at AAA in month 1, and AAA in month 2, it will show 1 for each month for that, or 2 if you added them together.

For the total though, it will count  the AAA as 1 as it goes back through the data.

EDIT: I downloaded and looked at your file. Your original measure was this:

code count =
SUMX(
    SUMMARIZE(
        '2020',
        '2020'[NUMERO DE CARGA FINAL]
    ),
    CALCULATE(
        DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] )
    )
)

I wasn't sure what that was doing. (I know what the code is doing, I wasn't following the logic of why SUMMARIZE and CALCULATE were in there). I replaced it with this

code count = DISTINCTCOUNT( '2020'[NUMERO DE CARGA FINAL] )

And got the same "wrong" result, so now we have a simple measure to expand to handle the total.

Then I used HasOneValue to do the actual math I think you want.

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

So if the month has one value, do a distinct count. If not, it is a total, and do a distinct count on the combination of the month and the numero d carga final. It returns this:

Is that what you are expecting?

[Andvil]

Hi @edhans,

Yes that is what I want, I created the formula you mention and it calculates the total I want. I still have a problem, when I filter according to months, for example to see only the first three months of the year, it does not consider the filter and the total considers all the information.

For example, 82210 is the total of 5 months, but I want to see only 3 months and as you can see in the picture, the total is not considering only the three months above.

Can you help me to solve this please?

Best, 

JALV

[Andvil]

Thank you @edhans 

But i should not have the same code in two months. As it is an individual sales code, it should not be duplicated in another month.

Also, how can I make the total count the values above it?

Thank you for your help,

JALV

[edhans]

---

@Andvil wrote:

Thank you @edhans 

But i should not have the same code in two months. As it is an individual sales code, it should not be duplicated in another month.

---

FWIW @Andvil  you have many codes in more than one month.

Just one code is in month 1, 2, and 5.

[edhans]

@Andvil Was wondering if you had a chance to look at this. Either the field is not the correct one, or there are dupes in your data where you were not expecting them to occur. In which case, my original explanation was right and I believe the solution I provided does return the expected results, even if your data is not what you expected.

Could you review and either mark this thread as solved, or post back and let me know what I may be misunderstanding so I can further assist? 

Thanks!

[Andvil]

Hello @edhans,

Thank you for letting me know there are codes that are repeated in other month. I will have to check that. For now I would like to consider them as if it would be correct.

I Tried your formula and it works, now i just want the total to sum only the months that are filtered. 

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.

Thank you for all your help!

Best,

JALV 

[edhans]

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

[Andvil]

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

[edhans]

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

[Andvil]

 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

[edhans · Accepted solution]

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.

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.

[Andvil]

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

[kriscoupe]

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

[Andvil]

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

[edhans]

Great @Andvil - glad I was able to help.

[Andvil]

Hi @edhans,

I was using the sumx formula you suggested and it all looked great. Today I realized there is a mistake (not really a mistake, but when I asked for help I created a simpler example than the one I work with, my real report requires more).

So, in my real report, I use multiple filters. When filtering by month or sales code I get the distinct count total correct (considering row by row values), but when I filter according to any other variable, such as product, country, customer, transport, etc... the distinct count total value does not match the row by row sum. 

I tried with all the formulas and the ones with the Hasonevalue but I get mistakes either when filtering by sales code or by month.

Can you help me with this issue please, I attach the file so you can better understand and visualize what I am saying: Distinct Count Example 2 

I tried adding the filter variables in the sumx and summarize formula and still got the incorrect total. I know you suggested me not to use summarize formula but I tried to see if that formula works in this case.

I would really appreciate your help.

Best,

Jal