cancel
Showing results for
Search instead for
Did you mean:
abeinke2018 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
v-yulgu-msft 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")

How to Get Your Question Answered Quickly

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

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 96 members 1,542 guests
Recent signins:
• • JeffJ60 • jessiang81 • admin_xlsior • Carnivean • caond • geostation Please welcome our newest community members:
• kong • Henle666 • ashagopal • camiloh • MichelleDz • mssql2014 • swati1542 