Forum Discussion
Create table based on DAX
Hi community!
I have a data with 1 dimensional table and different transaction tables.
- Dim 1 : Country, Department, Team, Product
- Calendar table
- 1. Transaction Table: Sales
- 2. Transaction Table: Revenue
- 3. Transaction Table: Costs
The tables are linked by Product and Date.
Now I would like to create a calculated table that contains this format:
| Country | Area | Team | KPI | Level | Value |
| France | AA | A1 | Sales | Good | 5 |
| France | AA | A1 | Sales | Neutral | 6 |
| France | AA | A1 | Sales | Bad | 8 |
| France | AA | A1 | Costs | Good | 8 |
| France | AA | A1 | Costs | Neutral | 6 |
| France | AA | A1 | Costs | Bad | 5 |
As you can see we have 3 columns with the dimensions Country, Area and Team.
Then we have the specific KPI (Sales, Costs, Revenue) with a specific "Level" and value.
The value shows the frequence/ bumber of weeks we have been able to reach a specific goal:
Sales
- Good: Sales above 500
- Neutral: Sales 200-500
- Bad: Sales below 200
How is this possible with dax?
I don't need this information as a matrix, that would be easy.
I need this as a calculated table.
I am grateful for every help!
Hi joshua1990 ,
I created some data:
For example:
Transaction Table: Costs
When the three table structures of Transaction Table: Sales Transaction Table: Revenue Transaction Table: Costs are the same, we can use the Append method.
Here are the steps you can follow：
1. Go to Power query and select Append Queries -- Append Queries as New.
Enter the Append interface, select "Three or more tables", add three tables.
Result：
2. Select the created new table Append1, select [Sales],[Revenue],[Costs] – Unpivot Columns.
3. Select the two columns in the new data and change the names to "KPI", "Amount”.
4. Create calculated column.
Level = SWITCH( TRUE(), 'Append1'[Amount]<=200,"Bad", 'Append1'[Amount]>200 &&'Append1'[Amount]<=500,"Neutral", 'Append1'[Amount]>500,"Good")Value = COUNTX(FILTER(ALL('Append1'),'Append1'[KPI]=EARLIER('Append1'[KPI])&&'Append1'[Level]=EARLIER('Append1'[Level])),[Level])5. Create calculated table.
Table = SUMMARIZE('Append1', 'Append1'[Country],'Append1'[Area],'Append1'[Team],'Append1'[KPI],'Append1'[Level],'Append1'[Value])6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Can you provide some sample data to work with?
Hi joshua1990 ,
I created some data:
For example:
Transaction Table: Costs
When the three table structures of Transaction Table: Sales Transaction Table: Revenue Transaction Table: Costs are the same, we can use the Append method.
Here are the steps you can follow：
1. Go to Power query and select Append Queries -- Append Queries as New.
Enter the Append interface, select "Three or more tables", add three tables.
Result：
2. Select the created new table Append1, select [Sales],[Revenue],[Costs] – Unpivot Columns.
3. Select the two columns in the new data and change the names to "KPI", "Amount”.
4. Create calculated column.
Level = SWITCH( TRUE(), 'Append1'[Amount]<=200,"Bad", 'Append1'[Amount]>200 &&'Append1'[Amount]<=500,"Neutral", 'Append1'[Amount]>500,"Good")Value = COUNTX(FILTER(ALL('Append1'),'Append1'[KPI]=EARLIER('Append1'[KPI])&&'Append1'[Level]=EARLIER('Append1'[Level])),[Level])5. Create calculated table.
Table = SUMMARIZE('Append1', 'Append1'[Country],'Append1'[Area],'Append1'[Team],'Append1'[KPI],'Append1'[Level],'Append1'[Value])6. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
