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