cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abeinke2018 Frequent Visitor
Frequent Visitor

sumifs formula with related tables

Data

 CalendarTable CropDataExcel Data

 

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

 

 

 

1295.jpg1288.jpg1289.jpg

2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: sumifs formula with related tables

Hi @abeinke2018,

 

1.PNG

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
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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 96 members 1,542 guests
Please welcome our newest community members: