Count formula based on single criteria in same table

[kwhite0510]

Good Afternoon,

I have a dataset that has sales agreements listed. Currently if the agreement has mulitple products listed, it will repete the total quantity (ie. duplicating it). I need a count equation that will return the number of lines/rows in an agreement with the criteria being that we only need to count the line PER agreement. For example, I need it to return "3" if there are 3 lines for agreement number 0100755.

Once I have the count of rows per agreement I would like to use the following formula to receive a result of quantity per line to avoid duplication of total quantity. 

= CALCULATE(SUM('Sales agreements'[Agreed quantity])/SUM('Sales agreements'[Count]))

If anyone can make a recommendation I would greatly appreciate it.

Thank you in advance.

[Ajendra]

Hello,

I hope this message finds you well!!

Here is the dummy data which i took to execute this DAX.

Here is the dax i tried. check this once.

[kwhite0510]

@Ajendra I believe this is on the right track for what I need! However, my formula is not showing valid. 

Within this formula I have a formula for "Total Qnty"

Please let me know if you see what could be wrong. Thank you very much

[kwhite0510]

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!

[Wilson_]

Total 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?)

[kwhite0510]

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.

[kwhite0510]

Anyone have any insight? I am still struggling to get this to work. Thank you in advance. 

[Wilson_]

Okay, but what does agreement #6100489 show in the "Agreed quantity" and "Agreed quantity line" columns? Is it also repeated there?

[kwhite0510]

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.

[Wilson_]

Hello kwhite0510,

Try the below as a measure, instead of your two step process. What the measure does is summarize the table first to eliminate the duplicate rows.

Total Agreement Quantity =
VAR AgreementSummaryTable =
SUMMARIZE (
    'Agreements',
    'Agreements'[Agreement Number], 'Agreements'[Total Quantity]
)

RETURN
SUMX (
    AgreementSummaryTable,
    'Agreements'[Total Quantity]
)

It should work in a visual that has Agreement Number in the rows. If that doesn't work for you, please share a snippet of your agreement table and a sample report page you'd be using this calculation on.

----------------------------------
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?)