Create table based on DAX

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