Forum Discussion
Count formula based on single criteria in same table
Thank you Wilson_ The formula above is valid however did not produce the results needed. Below is an example of the data. The agreement can have quantity in either the "agreed quantity" or the "agreed quantity line" columns so those need added together. Once that is complete I need the total sum (in this case 394) divided by 6 lines and amount shown as 49 in each row for the total agreement quantity. Therefore when I total all agreements we are not overstated. Any and all help is MOST appreciated as I have been trying to get this for months! Thanks!
Memorable MemberTotal Agreement Quantity =
VAR AgreementSummaryTable =
SUMMARIZE (
'Agreements',
'Agreements'[Agreement Number], 'Agreements'[Agreed Quantity] + 'Agreements'[Agreed quantity line]
)
RETURN
SUMX (
AgreementSummaryTable,
'Agreements'[Agreed Quantity] + 'Agreements'[Agreed quantity line]
)Edited to add the two columns together. However, I'm not following on why agreement quantities are being counted multiple times. It doesn't appear there is any overlap between different lines for an agreement.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Thank you Wilson_ Here is an example of what the problem is. Some agreements have a total quantity and some have a quantity per item line. You can see in the screenshot below that there should be 75 units total for the 4 lines of the agreement. It is repeating the number of units and I need a total that will divide 75 by the number of lines in the agreement. Therefore it should show 18.75 in the total agreed qnty column so the total will reflect the 75 when added together.
- Wilson_
Okay, but what does agreement #6100489 show in the "Agreed quantity" and "Agreed quantity line" columns? Is it also repeated there?
My apologies. I typed out a message last week but does not appear it saved.
If the quantity appears in "Agreed Quantity" it repeats the total amount vs. "Agreed quantity line" where each line has the quantity. This is dependent on type of agreement entered which is what makes it difficult to get a grand total wtihout duplicates. Below image shows where the total is duplicated. This is why I need a count type formula that would divide total tonnage on the ones that have totals duplicated.
Anyone have any insight? I am still struggling to get this to work. Thank you in advance.