Table total not calculating correctly

DAX DAX Calculations
1

Hi
I have a table with 3 columns - each calculating the value of an opportunity.
[Base Opp Value] - calculated opportunity value based on quotedetail table values and filters
[Total Revenue] - value stored on the actual opportunity
[Opp Value] - calculated value based on IF function. Ie if [Base Opp Value] is blank then use [Total Revenue] else [Base Opp Value]

In the table the [Opp Value] total is incorrect

I’ve looked at the video for fixing totals but it doesn’t appear to work in my scenario in that it isn’t taking into account any of the filters associated with the opportunity in the [Opp Value] column

2

Hi @AliB. I’m not sure to which video you are referring, but in any case, perhaps the “Fix Incorrect Totals” DAX Pattern post might help.
Fix Incorrect Totals - DAX Patterns - Enterprise DNA Forum

If you still have issues, please upload your work-in-progress PBIX, your dataset, and a marked-up screenshot or Excel mockup of the visual you are having issues with for the forum members to analyze.
Greg

3

PB Test Model.pbix (145.7 KB) TestModel - Copy.xlsx (16.2 KB)

Thanks Greg,
I’ve looked at the article but still have an issue with the totals - I’ve attached the model and the expected results in the excel file.

4

Hello @AliB,

Thank You for posting your query onto the Forum.

Here’s the solution and the measures provided for the reference to achieve the correct totals -

1). Measure For Total Value - Totals -

Total Opp Value - Totals = 
SUMX(
    SUMMARIZE(
        Opportunity , 
        Opportunity[OppID] , 
        "@Totals" , 
        [Total Opp Value] ) , 
    [@Totals]
)

2). Measure For Total Open Opp Value - Totals -

Total Open Opp Value - Totals = 
SUMX(
    SUMMARIZE(
        Opportunity , 
        Opportunity[OppID] , 
        "@Totals" , 
        [Total Open Opp Value] ) , 
    [@Totals]
)

3). Opps per Group using Total Opp Value - Totals -

Opps per Group using Total Opp Value - Totals = 
SUMX(
    SUMMARIZE(
        account , 
        account[AccountID] , 
        "@Totals" , 
        [Opps per Group using Total Opp Value] ) ,
    [@Totals]
)

4). Measure for Aged Opp Value - Totals -

Aged Opp Value - Totals = 
SUMX(
    SUMMARIZE(
        account , 
        account[AccountID] , 
        "@Totals" , 
        [Aged Opp Value] ) , 
    [@Totals]
)

Lastly, here’s the screenshot of the results that you were trying to achieve.

Final Output
Final Output1337×803 56.2 KB

I’m also attaching the working of the PBIX file for the reference as well.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Important Note: This technique is already showcased in the post which @Greg had provided earlier. You can go through that post again for the reference.

Thanks and Warm Regards,
Harsh

PB Test Model - Harsh.pbix (150.7 KB)

3 Likes
5

Thanks Harsh - In the article it was saying not to use the Summarize method or at least be wary of it. Is there a reason you have preferred to use that method?

There’s just one element that hasn’t come through and that’s the aged buckets that have a total of zero are not showing on the report

6

Hello @AliB,

For the first point, this is always my preferred approch (as far as I’m concerned) since it’s easy to write and evalute the results. Also Sam Sir has used this approach in many of his videos while showcasing the technique about how to fix the totals. Most of my solutions pertaining to the fixing of the totals uses this approach.

And this was the note put in by @Greg -

NOTE: SUMMARIZE can also be used in place of ADDCOLUMNS, but be aware that when using calculated columns, it can lead to inconsistent results and performance issues.

And if you observe carefully, it’s pertaining to the calculated columns but we’re simply using the measures.

For second point, if you want to see the zero’s in your aged buckets than in that case it’s a simple fix. Just fix your initial or base measures. Here’re the formulas provided for the reference -

Opps per Group using Total Opp Value = 
VAR _Opps_per_Group_using_Total_Opp_Value = 
CALCULATE( [Total Open Opp Value],
   FILTER( Opportunity,
        COUNTROWS(
            FILTER( 'Aged Opportunities',
                [Days Open] >= 'Aged Opportunities'[Min] &&
                [Days Open] <= 'Aged Opportunities'[Max] )) >0))

RETURN
IF( ISBLANK( _Opps_per_Group_using_Total_Opp_Value ) , 
    0 ,
    _Opps_per_Group_using_Total_Opp_Value )



Aged Opp Value = 
VAR OppVal = [Opps per Group using Total Opp Value]

VAR TotalOppVal = 
SUMX(
    ADDCOLUMNS(
        VALUES( account[AccountID] ) ,
            "TotalOpp" , [Total Open Opp Value] ,
            "1-30" , [Total Open Opp Value] ,
            "31-60" , [Total Open Opp Value] ,
            "61-90" , [Total Open Opp Value] ,
            "91-120" , [Total Open Opp Value] ,
            "121-180" , [Total Open Opp Value] ,
            "180+" , [Total Open Opp Value]
        ),
    [TotalOpp]
) 

VAR _Results = 
IF( ISBLANK( OppVal )  , 
    0 , 
    OppVal )

RETURN
IF( HASONEVALUE( account[AccountID] ) , _Results ,
    TotalOppVal )

Hoping this helps you. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

PB Test Model - Harsh v2.pbix (149.3 KB)

4 Likes
7

Hi @AliB, did the response provided by @Harsh and @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

8

Hi @AliB, we’ve noticed that no response has been received from you since the 4th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

9

Hi @AliB, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

10

Hi Harsh
Apologies for the delay in replying. Your solution worked perfectly!

Thank you