Forum Discussion

ConnieMaldonado's avatar
ConnieMaldonado
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Create a Table via DAX based on another table

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/1btS457OqSeBEZIvrnH9gb29l...
Need Help
  • ConnieMaldonado's avatar
    ConnieMaldonado
    3 years ago

    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!

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
3 years ago

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's avatar
ConnieMaldonado
Icon for Responsive Resident rankResponsive Resident
3 years ago

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.