Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sanderson82
Helper I
Helper I

Sum filtered measures

Hi

I have a table showing per contract filtered measures for SaleM3, TotalM3 and Sale Value.  The sum of the SaleM3 and Total M3 are correct however the Sale Value total is not filtered to only what is showing on the table.  My plan is to have a card showing the total sale value for "Today" and compare this against previous day, previous week, MTD etc, please see below.  

 

SaleM3 = SUM('Contract Manager'[Sales m3])
TotalM3 = _Calculations[MAINM3] + _Calculations[D16M3] + _Calculations[PIPEM3]
Sale Value = [TotalM3]*[SaleM3]
 
MAINM3 = 
CALCULATE(
	SUM('MAINDrops'[Quantity]),
    FILTER(MAINDrops,MAINDrops[StatusID] = 2)
)
D16M3 = 
CALCULATE(
	SUM('D16Drops'[Quantity]),
    FILTER(D16Drops,D16Drops[StatusID] = 2)
)
PIPEM3 =
CALCULATE(
    SUM('PIPEDrops'[Quantity]),
FILTER(PIPEDrops,PIPEDrops[StatusID] = 2)
)
 
Sale Value Table.png
Any help much appreciated.  I've seen a few other posts in realtion to this however in any examples all the data is always in one table.  In my scenario I'm working across 4 tables and using measures to combine/calculate the values
2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @sanderson82 

Do you have four tables as below

Capture1.JPG

 

If tables have the same structure, we could append them to one table.

Would you like this method?

Any way, could you share the structures of four table so that we would move to next step?

(I need to know relationship among these tables, whether columns have the same numbers and names)

 

Best Regards
Maggie

 

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

Hi @v-juanli-msft  thanks for your reply.

 

The tables MainDrops, D16Drops and PipeDrops all have exactly the same structure.  It is basically 3 instances of the same application, each with their own SQL back end.
The table 'Contract Manager' I use as a central reference.  This is linked to 3 other tables, MainCustomers, D16Customers and PIPECustomers by a "Name" field.
There are then 3 "Site" tables (Main, D16, PIpe) where the above name is linked to an ID.  This ID is then linked to 3 "Jobs" tables (Main, D16, Pipe). by "JobID" field.

JobID for all 3 tables is then linked to 3 "Loads" tables.
ID in the 3 Loads tables is then linked to "LoadID" in the 3 Drops tables.

 

Hope that makes sense!!

I'm wondering could I join the 3 Drops tables as you have suggested and link this back individually to the 3 Loads tables?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.