Create table based on DAX

[joshua1990]

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!

[Anonymous · Accepted solution]

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

[tamerj1]

H @joshua1990 

Can you provide some sample data to work with?