Create a Table via DAX based on another table

[ConnieMaldonado]

Hello

I need to create a table in DAX based on another table which adds records based on conditions.  I created a mock pbix (link below)

https://drive.google.com/file/d/1btS457OqSeBEZIvrnH9gb29l28NJ9MB9/view?usp=sharing 

I have a sales report which displays data in a matrix based on several parameters:

Subtype

Category (from Mapping table) - either materials, labor, or split

Line Item (from Mapping table)

The data works great with the matrix, and I am able to display the information exactly as the user requested.

The issue is that for records that have a "split" Category, I need to allocate sales and quantity information into materials and labor and include it in the proper area of the report.

My thought was to create a table in Power BI via Dax that would add a record when Category = split, one record with Category = Materials, and the other with Category = Labor.  I could then do the allocation and update Sales and Qty.  

I created a pbix with mock data (link above).  Some of the totals aren't correct for the calculations (like Sales/Unit), but other than that it works.

Thanks.

[v-xiaotang]

Hi @ConnieMaldonado 

Thanks for reaching out to us.

>> The issue is that for records that have a "split" Category, I need to allocate sales and quantity information into materials and labor and include it in the proper area of the report.

please give the expected output from the given sample file

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[ConnieMaldonado · Accepted solution]

I have determined a way to do this which involves manipulating the data in Power BI to allow use of the matrix in the way that I want.  Thanks for reviewing!

[ConnieMaldonado]

Thanks so much for looking at this, and my apologies for not including the results in my original post.  Note that I made updates to the pbix and reposted - same link as before.

For items with Category = “Split”, I need to allocate a portion of Sales and Qty to both Labor and Materials.  I need the line items to appear in the correct section (labor/materials) of the report.

Here's what the report looks like now:

I need it to look like this (what's shown below is for Class 1 only):

The allocation % is calculated using the total quantity and a Factor from the Mapping table.  I added the calculations in the pbix.  I’m having an issue with one of the calculations, but I can figure that out later.

I have included what the results should look like above based on the sample data. 

Note that I updated the factors in the mapping table slightly to give more realistic results.

Thanks again for any help.