cancel
Showing results for
Did you mean:
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.

4 REPLIES 4
Super User

## Re: Creating a percent of a column subtotal

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

Regular Visitor

## Re: Creating a percent of a column subtotal

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]))`

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 192 members 2,358 guests
Recent signins: