Still can't get table to SUM up

[jetform_consult]

Good day All...

I've been wrestling with trying to get my totals to match for over 3 months, but with no success.  I have watched COUNTLESS videos explaing various ways to use SUMX, CALCULATETABLE, and vTables.  My measure seems to be doing it correctly, except for 1 store...All other totals are correct and I'm looking for an explanation as to why 99% of totalling works with the measure, all but a single store.

My measure reads as follows:
In which I have 2 different __VAR methods...Both which fail to correctly sum up the total correctly FOR JUST A SINGLE STORE.

AckKNBelopp_156Ack = 
VAR __CurrentAck = DISTINCT('Report156'[156AckTotalsSortby])
VAR __TempTable=        
        CALCULATETABLE(
                    ADDCOLUMNS(
                                SUMMARIZE(Report156_Ack;Report156_Ack[Period];Report156_Ack[ButikRegion];Report156_Ack[Butik];Report156_Ack[Leveranssätt];Report156_Ack[Saljkanal]);
                                "SummedKB";[AckKnBel]
                              )
                      )
VAR __VAR1=        
               CALCULATE(
                   SUMX(__TempTable;[SummedKB]
                              )
                             )
VAR __vTable=        
        ADDCOLUMNS(
                    CROSSJOIN(
                                VALUES(Report156_Ack[Period]);
                                VALUES(Report156_Ack[ButikRegion]);
                                VALUES(Report156_Ack[Butik]);
                                VALUES(Report156_Ack[Leveranssätt]);
                                VALUES(Report156_Ack[Saljkanal])
                              );
                    "@SummedKB";[AckKnBel]
                    )
VAR __VAR2=        
               CALCULATE(
                   SUMX(__vTable;[@SummedKB]
                              )
                             )
                             
VAR __VAR3 = Sum(Report156[AckKNBelopp])
Return
IF(HASONEFILTER(Report156[156AckSortby]);
    Round(__VAR3;0);
    ROUND(__VAR2;0)
)

 Any tricks to get this single store to fall in line, without compromising the other correctly calculated totals, would be deeply appreciated.

[v-venuppu]

Hi @jetform_consult ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @jetform_consult ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @jetform_consult ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ZhangKun @johnt75 @Deku @bhanu_gautam  for the prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[ZhangKun]

This problem is due to Auto-Exist, you can check the link below to understand.

Understanding DAX Auto-Exist 

The short answer is that you should always use dimension tables.

[jetform_consult]

Thanks for your reply...I'll try TREATAS and see what happens.

[johnt75]

The first thing I would check is that HASONEFILTER is behaving as you expect it to. Change the returns to e.g. 1 and 2 so you can easily identify the behaviour on different rows. Alternatives to HASONEFILTER could be HASONEVALUE and ISINSCOPE, though I am unsure as to how ISINSCOPE works with sort by columns.

The second thing I would try is removing the rounding to return the full result. The difference between the total shown and the actual total is small enough to be simply rounding error.

[Deku]

It is off by 1 and you using ROUND(). The Total is calculated independly of the other rows in the Table. Remove the Round and check to see if it adds up correctly

[bhanu_gautam]

@jetform_consult Try using

AckKNBelopp_156Ack =
VAR __CurrentAck = DISTINCT('Report156'[156AckTotalsSortby])
VAR __TempTable =
CALCULATETABLE(
ADDCOLUMNS(
SUMMARIZE(
Report156_Ack,
Report156_Ack[Period],
Report156_Ack[ButikRegion],
Report156_Ack[Butik],
Report156_Ack[Leveranssätt],
Report156_Ack[Saljkanal]
),
"SummedKB", [AckKnBel]
)
)
VAR __VAR1 =
CALCULATE(
SUMX(__TempTable, [SummedKB])
)
VAR __vTable =
ADDCOLUMNS(
CROSSJOIN(
VALUES(Report156_Ack[Period]),
VALUES(Report156_Ack[ButikRegion]),
VALUES(Report156_Ack[Butik]),
VALUES(Report156_Ack[Leveranssätt]),
VALUES(Report156_Ack[Saljkanal])
),
"@SummedKB", [AckKnBel]
)
VAR __VAR2 =
CALCULATE(
SUMX(__vTable, [@SummedKB])
)
VAR __VAR3 = SUM(Report156[AckKNBelopp])
RETURN
IF(
HASONEFILTER(Report156[156AckSortby]),
ROUND(__VAR3, 0),
ROUND(__VAR2, 0)
)

[jetform_consult]

Thanks for your reply.

I made your suggested code changes, but to no avail.

I am attempting to bring our RS156 report into PowerBI, but can't until I get this totaling issues sorted.