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
Anonymous
Not applicable

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
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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 others find it more quickly.
Anonymous
Not applicable

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.

AlB
Super User
Super User

Hi @Anonymous

 

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

Anonymous
Not applicable

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.