cancel
Showing results for
Did you mean:
Frequent Visitor

## sumifs formula with related tables

Hi All,

I am new to BI and have been using excel extensively, my issue is trying to sum amounts by day (given there is 000's of lines of data)

The types of formulas i have used are as below ...

Crop = CALCULATE(SUM(CropData[Kg]),FILTER(RELATEDTABLE(CropData),CropData[Date])<='Total Harvest'[Date]&&RELATEDTABLE(CropData),CropData[Date]>='Total Harvest'[Date])

Harvest = CALCULATE(SUM(CropData[Kg]),ALL('Total Harvest'[Date]))

What I am trying to get is the following (Excel)

=SUMIFS(L:L,B:B,">="&"Table Calander[Date]",B:B,"<="&"Table Calander[Date]",G:G,"GH 4")

Trying to reference the date field within the calendar table in order to summarise

Any help would be apreciated

Thankyou

Andrew

2 REPLIES 2
Super Contributor

## Re: sumifs formula with related tables

Hi @abeinke2018,

What is the intention of above formulas?

Besides, to illuatrate the formula you used in Excel, please show us the desired result.

=SUMIFS(L:L,B:B,">="&"Table Calander[Date]",B:B,"<="&"Table Calander[Date]",G:G,"GH 4")

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: sumifs formula with related tables

I have this formula which gives me what I need, unless you know of a better way to do this ?

It sums up all the Kg's (000's of rows) for the location GH4 for each day

Field GH4 = CALCULATE(SUM(Field[GH4]),FILTER(ALL(Field),Field[Day]<='Total Harvest'[Date]&&Field[Day]>='Total Harvest'[Date]))

Thankyou

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 96 members 1,542 guests
Recent signins: