Forum Discussion
Compile table from multiple tables in DAX
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 :smileysad:
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
Hi,
Try these steps:
- Append data from all 3 Tables to create 1 large range
- Remove 3 columns - Number of staff, Number of Machines and Premises Size
- Select the first two columns and select Unpivot other columns
- Click on Close and Apply in the Query Editor
- Build a Matrix visual and drag File Date, Customer ID to the row labels. Drag Attribute column to the column labels
- Write this measure: Scores = SUM(Data[Value])
Hope this helps.
3 Replies
Hi,
Try these steps:
- Append data from all 3 Tables to create 1 large range
- Remove 3 columns - Number of staff, Number of Machines and Premises Size
- Select the first two columns and select Unpivot other columns
- Click on Close and Apply in the Query Editor
- Build a Matrix visual and drag File Date, Customer ID to the row labels. Drag Attribute column to the column labels
- Write this measure: Scores = SUM(Data[Value])
Hope this helps.
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!
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.
