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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 206 members 2,532 guests
Please welcome our newest community members: