cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jdriscol Regular Visitor
Regular Visitor

Creating a percent of a column subtotal

Hi,

In a file I'm working on, I have a fact table with year, plant, machine, part number, and part quantity produced. In a separate table (not sure if it'd be considered fact or dimension table), I have a table with year, plant, machine, and machine maintenance cost. Basically, a machine can be in one or more sites in a given year and it will have a separate associated cost in each location. Each machine can also produce multiple parts, and any part can be produced on multiple machines in multiple locations. I would like to have a matrix table with part ID in the rows, plant in the columns, and a machine maintenance cost per quantity produced for the value. I've linked the two tables by a concatenated plant-machine-year column and the values are unique in the cost table. Please help with creating a measure for this value I'm looking for.

 

I called this thread "Creating a percent of a column subtotal" because I assume the best course is to create a column in the fact table that represents the portion of production of each row relative to the overall plant-machine-year combination, but this may not even be the best way to achieve what I'm looking for.

 

I can share a sample file I created to illustrate what I'm working on, but not sure how to upload it here.

Thanks in advance!

4 REPLIES 4
Super User
Super User

Re: Creating a percent of a column subtotal

Hi @jdriscol

 

Yes, it would really help if you share the pbix

You cannot upload, you have to share the URL to the file hosted elsewhere. Dropbox, Onedrive... or just upload it to a site like tinyupload.com (no sign-up required).

jdriscol Regular Visitor
Regular Visitor

Re: Creating a percent of a column subtotal

Community Support Team
Community Support Team

Re: Creating a percent of a column subtotal

Hi @jdriscol ,

 

Just change the direction of the relationship between the test table and the fact table to both. We can create a measure to get the result we need.

 

AVE = CALCULATE(SUM(TestCosts[Maint Cost]))/CALCULATE(SUM(FactTable[Quantity]))

Capture.PNG

Please check the pbix as attached.

 

Regards,

Frank

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

Re: Creating a percent of a column subtotal

Thank you for the response. Since the original post, I was able to figure out a solution to my problem. To your point, I did have to have a bi-directional relationship between the fact table and the cost dimension, but I also needed to create a calculated column in the fact table and two measures to get to what I needed. I can post the sample file with the analog of what I did to my actual project if you wanted to compare (the numbers I get in my table are slightly different). I'm still not sure how to attach files to these posts, though.