Forum Discussion
Count formula based on single criteria in same table
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.
9 Replies
Wilson_Memorable Member
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?)- Ajendra
Resolver I
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.
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
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?)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.
