Column Total Incorrect

[kwhite0510]

Hi Everyone,

I have this equation however, it will not provide a cumlative total. Can someone look at the below DAX Equation and let me know if there is anything I can add or do differently to get it working?

Total Agreement Quantity = CALCULATE(Divide('Sales agreements'[Contracted Total],'Sales agreements'[Count rows]))

Thank you!

Kate

[PhilipTreacy]

Hi @kwhite0510 

You're seeing that total because your measure is working in that cell too.  So, because you are dividing by 'Sales agreements'[Count rows], I'm guessng this value is coming out to all the rows in the table.  Hence the unexpectedly low value.

The Total cell doesn't always add up the values above it, in this case it applies the same DAX measure as the rows above it in the column.  It's one of those frustrating annoyances with DAX that is counter-intuitive.

If you can provide your data, or at least a snapshot, I can ry fixing this.  At the moment I dno't know what any of your other columns look like.

Regards

Phil

[kwhite0510]

Thanks so much @PhilipTreacy Here is some of the data. Let me know if this is enough for you to help revamp the formula.

Total Agreement Quantity = CALCULATE(Divide('Sales agreements'[Contracted Total],'Sales agreements'[Count rows]))

Sales agreements'[Contracted Total] - from the equation above this equation is as follows:

Contracted Total = Calculate(SUMX('Sales agreements','Sales agreements'[Agreed quantity line]+'Sales agreements'[Agreed quantity]))

Sales agreements'[Count rows]))from the equation above this equation is as follows:

Count rows = Calculate(Countrows('Sales Agreements'),Filter('Sales agreements','Sales agreements'[Agreement number]='Sales agreements'[Agreement number]))

[Anonymous]

Hi @kwhite0510 ,

According to your screenshot, I think you should have hide some other columns in your table visual.

As far as I know, [Contracted Total] and [Count rows] are measures, so there should be some key columns like ID, Category to let your visual expand. You can create a measure measure with correct subtotal.

New Total Agreement Quantity =
SUMX (
    SUMMARIZE (
        'Sales Agreements',
        'Sales Agreements'[Column1 in table visual],
        'Sales Agreements'[Column2 in table visual],
        ...
    ),
    [Total Agreement Quantity]
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kwhite0510]

@PhilipTreacy here is some sample data. What I am trying to do is create an total per agreement number. As you can see the numbers are duplicated in the data. Agreement number 6100490 should show in a total column of 25 per each line. 50 is the total but duplicated. Agreement number 6100489 will show 75 per line. But when the total column shows a grand total it should eliminate duplicates. 

[PhilipTreacy]

Hi @kwhite0510 

I need the data that those measures use to work, otherwise I have to recreate it all.  Can you post an excerpt of the table(s) as a table, not an image?

Regards

Phil

[kwhite0510]

Agreement Number	Agreed Qnty	Agreed Qnty Line	Item
6100489	0	75	123
6100489	0	75	124
6100489	0	75	125
6100489	0	75	126
6100489	0	75	127
6100490	50	0	127
6100490	50	0	126

[kwhite0510]

Screenshot of the column