cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NBOnecall Member
Member

Three tables to get total Gross Profit between Items and Kit Items.

Hi, 

 

I have three tables that I am working with. Here is a pbix file that has these tables if it is needed to help me.

 

One is the list of sales and this can include single items or items combined together to make kits for those sales. 

Sales Table.PNGOne table that is the Kits and the member items (items that make up the kit). It also has the percent I want to allocate the above Kit Sales for the gross profit. 

KitSKU Table.PNG

The last table is the average value of inventory throughout the year of all items.

Average Value Table.PNG

I would like an output as the screen shot below shows. The Gross Profit should be the sum of the individual item sales and then the percent of allocation of the Kit sales added to it.

 

So in this example the Speaker is 1075 = (200*3)+(500*.95) and the Mount is 65 = (20*2)+(500*.05). I then want to pull in the average value of the items. 

Expected Output.PNG

I have tried and tried to figure out a way of doing this and I can't get the to value to sum (Kit percentage and then the regular item sales).

 

This is the output that I was able to get in PowerBi in two separate Table Visualisations, I need it in one though.What I can get.png

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Three tables to get total Gross Profit between Items and Kit Items.

HI @NBOnecall ,

Please check below attach file if it suitable for your requirement.

14.png

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

View solution in original post

6 REPLIES 6
NBOnecall Member
Member

Re: Three tables to get total Gross Profit between Items and Kit Items.

Still looking for help. Thanks!

Community Support Team
Community Support Team

Re: Three tables to get total Gross Profit between Items and Kit Items.

Hi @NBOnecall ,

I'd like to suggest you to take a look at following blog to know how to deal with parent-child relationships in power bi:

Parent-Child Hierarchies

Please understand that this link is provided with no warranties or guarantees of content changes, and confers no rights.

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
NBOnecall Member
Member

Re: Three tables to get total Gross Profit between Items and Kit Items.

@v-shex-msft So when I go down that route to get the path of my Kit SKU (Child/Parent) it gives me "The value '9005' in 'KitSKU Table'[Kit Name] must also exist in 'KitSKU Table'[Member Item]. Please add the missing data and try again."

 

My Member Item is not part of the table under the column Kit Name. My Kit Name column is only the kits and then Member Item column tells what member's are apart of that kit. 

 

Also reading through that article it doesn't look like it address me then spreading out the precentage back into the table "Sales." This is where all the financial data is. 

 

Thanks. 

NBOnecall Member
Member

Re: Three tables to get total Gross Profit between Items and Kit Items.

Am I missing something that is super easy? 

Community Support Team
Community Support Team

Re: Three tables to get total Gross Profit between Items and Kit Items.

HI @NBOnecall ,

Please check below attach file if it suitable for your requirement.

14.png

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |

View solution in original post

NBOnecall Member
Member

Re: Three tables to get total Gross Profit between Items and Kit Items.

Yep this is perfect. Seeing the code and reading through it is a lot easier to me to understand then trying to read an article realted to a different process that doesn't quite fit my problem. It makes it more repeatable in the future and able to reference something that I understand. Thanks!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 312 members 3,236 guests
Please welcome our newest community members: