Forum Discussion
Need To Calculate A Total For A Column That Is Based On Multiple Rows In The Database Table
Hello Anonymous
This measure is giving me the results I expected
Seeded Acres = SUMX ( SUMMARIZE('Table','Table'[ID],'Table'[Year],'Table'[Grain]), CALCULATE( MAX ( 'Table'[Acres Seeded] ) ) )I expanded your sample with another year and another contract to test:
I have also attached my sample .pbix for you to look at.
Super UserAnonymous
Does a straight SUM(YourTable[Acres Seeded]) not return the correct amount? Not sure why you are using SUMX, what am I missing?
No, a simple SUM doesn't work. It gives the same result.
This data is based on filtering out over 64,000 lines of contracts - each contract line may have a seeded acres value based on another table's data that list seeded acres by producer, year and commodity.
The actual background table has multiple lines that make up the total contract tonnage (thus the contract count column). That background table is based on the original contracts table combined with a seeded acres table (that table has Producer ID, Year, Commodity and Acres Seeded).
The Contract Table is like this (stripped down to relevant columns):
| ID | Year | Grain | Contract Quantity | Acres Seeded |
| 12345678 | 2018 | Canola | 100 | 500 |
| 12345678 | 2018 | Canola | 50 | 500 |
| 12345678 | 2018 | Wheat | 150 | 1000 |
| 12345678 | 2018 | Wheat | 250 | 1000 |
As you can see, the total contract quantity for Canola is 150 tonnes, but the seeded acres should only be 500. Wheat has 400 tonnes total but only 1,000 seeded acres. Thus, summing doesn't work because it sums the entire column when I only need one value per producer per year per commodity.
I did it this way to correct another issue, but it now causes issues on another tab.
Thank you!
- jdbuchanan71
Hello Anonymous
This measure is giving me the results I expected
Seeded Acres = SUMX ( SUMMARIZE('Table','Table'[ID],'Table'[Year],'Table'[Grain]), CALCULATE( MAX ( 'Table'[Acres Seeded] ) ) )I expanded your sample with another year and another contract to test:
I have also attached my sample .pbix for you to look at.
jdbuchanan71 That's awesome, thank you!
Would the below also work? - I figured this out a short time before you posted your solution. It seems to give me what I need.
Your solution is a bit more elegant and I can use that for some other things I need to show!Seeded Acres =SUMX(FILTER(GROUPBY('Table','Table'[Acres Seeded],'Table'[Year],'Table'[Grain],'Table'[ID]),'Table'[Acres Seeded]),'Table'[Acres Seeded])- jdbuchanan71
Anonymous
Yes, that works also and you don't need the filter portion of your measure:
Seeded Acres = SUMX( GROUPBY('Table','Table'[Acres Seeded],'Table'[Year],'Table'[Grain],'Table'[ID]), 'Table'[Acres Seeded] )Although I am having a hard time wrapping my head around what is happening with the context and why it is not doubling up on the [Acres Seeded]. It must have something to do with including it in the GROUPBY that is forcing it to consider the grouped value instead of each lines value.
Very interesting, thank you for sharing.