Compile table from multiple tables in DAX

[AlexDECG]

Hello everyone,

I am fairly new to Power BI and DAX and I have been trying to find a way to create a summary table from more tables in DAX with no success 

We want to be able to better schedule visits to customers premises. We came up with a long series of criteria that should help us decide such as number of staff, number of machines and premises size. Each criteria has a score. 

This is a simplified version of what we built so far.

Could you please point me in the right direction?

Thanks in advance

Alex

[Ashish_Mathur · Accepted solution]

Hi,

Try these steps:

1. Append data from all 3 Tables to create 1 large range
2. Remove 3 columns - Number of staff, Number of Machines and Premises Size
3. Select the first two columns and select Unpivot other columns
4. Click on Close and Apply in the Query Editor
5. Build a Matrix visual and drag File Date, Customer ID to the row labels.  Drag Attribute column to the column labels
6. Write this measure: Scores = SUM(Data[Value])

Hope this helps.

[AlexDECG]

Good morning!

Worked perfectly! Many thanks!

Just one last question and apologies for not making this clear in my initial request.

I need to divide the total by a factor to obtain a percentage, so the standard matrix total function does not work.

If I add the Total as a measure to the matrix then I get an extra total column before each of the other criteria.

Can I get "my" total showing in the matrix without the extra columns?

Thanks!

[Ashish_Mathur]

Hi,

You are welcome.  I do not understand your requirement.  At max, we can have a different calculation running in the Total column.  If that is what ou want, then share the expected result.