Need To Calculate A Total For A Column That Is Based On Multiple Rows In The Database Table

[Anonymous]

EDIT - Corrected Subject (sorry about that)

Hi folks,

I have a table of data that isn't cooperating. I can get some of my calculations to work (eg To be purchase in year), but not all. It may be a data modelling issue, but this is the one calculation that I haven't been able to fix.

I have a series of producers who sell product to us. From those Producers, we may get a number for the acres on which they produce a particular commodity (sometimes that data isn't provided to us). We can make multiple contracts with these producers.

I will bring up an example table of data based on a customer:

ID	Year	Commodity	Acres Seeded	Potential Production	Total Contract Quantity	To be Purchased in year	Contract Count
12345678	2015	Durum			190.507	-190.507	1
12345678	2015	Wheat			122.469	-122.469	1
12345678	2016	Canola	650	599.258	263.650	335.608	3
12345678	2016	Durum	450	510.087	80.821	429.266	1
12345678	2016	Wheat	850	1074.538	703.783	370.755	3
12345678	2017	Canola			731.159	-731.159	7
12345678	2017	Wheat			1135.037	-1135.037	9
12345678	2018	Canola			229.848	-229.848	6
12345678	2018	Wheat			758.472	-758.472	5
12345678	2019	Canola	800	737.549	79.412	658.137	1
12345678	2019	Peas	0	0	9.691	-9.691	1
12345678	2019	Wheat	1300	1643.411	114.883	1528.528	2

The issue is that I want to get a total of the Acres Seeded, but if I do a SUMX, I get 8,350 because it multiplies the rows by the number of contracts before giving me the summed result.

If I hit the 2019 slicer, it gives me 3,400 acres instead of the desired 2,100 acres. The 2016 slicer gives me 4,950 acres instead of 1,950.

I've tried variations of dividing with no success. This is probably (and hopefully) something really simple to fix. It's been very frustrating because I got my To be purchased in year working:

To Be Purchased In Year =

    CALCULATE(

        DIVIDE(

            SUMX('Agrosoft With Seeded Acres','Agrosoft With Seeded Acres'[Potential Production]),

            [Contract Count]

        )       

    )

     -    

    CALCULATE(

        SUMX('Agrosoft With Seeded Acres','Agrosoft With Seeded Acres'[Total Contract Quantity])

    )

Can anybody help me? I removed some other data. Hope this makes sense.

Thank you!

[jdbuchanan71]

@Anonymous 

Does a straight SUM(YourTable[Acres Seeded]) not return the correct amount?  Not sure why you are using SUMX, what am I missing?

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

@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.

[Anonymous]

Thank you for the correction - I saw that on another solution and included it because I was focused on adding in the additional columns so I could group the data properly. I thought the filter seemed a little strange - I would have tried removing it when I had time, but I have several things in my job that I needed to handle first.

As you indicated, the GROUPBY is the key in that. I don't need four lines for a single seeded acres value for the same ID, commodity, grain and year - I just need one. The GROUPBY resolves that by only giving me one.

Once again, thank you. You have no idea how frustrating it's been for me. A few hours ago, I had zero solutions, now I have two, including one that will be perfect on some other data with which I need to work!