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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
croberts21
Responsive Resident
Responsive Resident

How to sum a cost based on field in another related table.

I have PBI Desktop from March 2023. I am trying to sum costs with a wipcategoryid of "10" or "50". Wipcategoryid looks like a number but is a string.

I have these tables with these fields:

Table: Jobcost
jobid (the job number)
actcode (activity code)
actcost (actual cost, this is sometimes null)

 

Table: ActivityCode
ActivityCode (connects to Jobcost.actcode)
Wipcategoryid (what I need to filter on)

 

There are other tables that connect to Jobcost. 

 

The relationship between Jobcost.actcode and ActivityCode.Activitycode is many to many for some reason.

 

Columns in my report table viz: jobid, Postage (a measure or column)

 

What I need: For each jobid in the table viz, sum all Jobcost.actcost records where ActivityCode.WipcategoryID="10" or "50".

 

I have tried to copy the Wipcategoryid to my Jobcost table with this column: `WipCatId = RELATED(Activitycode[jcwipcatid])` but I got an error "The column 'ActivityCode[jcwipcatid]' either doesn't exist or doesn't have a relationship to any table available...'. When in my relationships I link Jobcost.actcode to ActivityCode.Activitycode. But RELATED() cannot be used in a many-to-many relationship, which I have. See https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand#limited-...

 

I have tried these as columns and measures added to the Jobcost table and none have worked:

 

Measure: PostFrtMeas = CALCULATE(SUM('Jobcost'[jcactcost]), OR(RELATED(Activitycode[jcwipcatid])="10", RELATED(ActivityCode[jcwipcatid])="50"))) The error I get is "Column 'jcactcost' in table 'Jobcost' cannot be found or may not be used in this expression."

 

Column: PostFrtCol = CALCULATE(SUM('Jobcost'[jcactcost]), OR(Activitycode[jcwipcatid]="10",ActivityCode[jcwipcatid]="50"))
The error I get for this is The error I get is "Column 'jcactcost' in table 'Jobcost' cannot be found or may not be used in this expression."

 

Any ideas how to get this to work? I can't seem to find any formulas in tutorials that make filter criteria based on a connected but different table. And the Microsoft documentation does not say whether or not other tables can be used in various expressions or formulas.

Thank you!

 

Update: Ok I have fixed this. I changed the source SQL to link the 2 tables Jobcost.actcode with Activitycode.Activitycode. I also had renamed Jobcost.jcactcost to actcost so that part is also fixed.

 

Please consider this closed.

0 REPLIES 0

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.