Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi community!
I have a data with 1 dimensional table and different transaction tables.
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
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!
Solved! Go to 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
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |