Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!
I have a problem I just can't seem to work my way around.
I have a unpivoted table (Table1) with a list of KPIs (e.g. Sales, Cost1, Cost2, Cost 3). I have another attribute column to tell me whether something is an acutal or a budget, and then a value column. I also have a fourth column with another attribute (e.g. department, so that the KPIs are repeated.
I can therefore create a table that gives me the actuals, the budget, and variance using a measure <-successful so far.
The problem is when I want to build a second table. I have a list of calculations that I want to create (e.g. measure1 = Cost1/Sales) for Actual and Budget. How do I build this in such a way that I can have a table with each KPI down the rows, and Actual, Budget and Variance in the columns?
Help me Obi Wan Kenobi
Solved! Go to Solution.
pls try to create a table
and create three measures
pls see the attachment below
Proud to be a Super User!
could you pls provide some sample data and expected output?
Proud to be a Super User!
Hello!
Here is my data:
Name | Attribute | Type | Value | |||
Dept 1 | Cost1 | Actual | x | |||
Dept 1 | Cost 2 | Actual | x | |||
Dept 1 | Sales1 | Actual | x | |||
Dept 1 | Sales2 | Actual | x | |||
Dept 1 | Cost1 | Budget | x | |||
Dept 1 | Cost 2 | Budget | x | |||
Dept 1 | Sales1 | Budget | x | |||
Dept 1 | Sales2 | Budget | x | |||
Dept 2 | Cost1 | Actual | x | |||
Dept 2 | Cost 2 | Actual | x | |||
Dept 2 | Sales1 | Actual | x | |||
Dept 2 | Sales2 | Actual | x | |||
Dept 2 | Cost1 | Budget | x | |||
Dept 2 | Cost 2 | Budget | x | |||
Dept 2 | Sales1 | Budget | x | |||
Dept 2 | Sales2 | Budget | x |
If KPI1% = Cost1/Sales1
and KPI2% = Cost2/Sales2
My query is how I would build this table:
Actual | Budget | Variance | ||||
KPI1% | x | x | x | |||
KPI2% | x | x | x |
pls try to create a table
and create three measures
pls see the attachment below
Proud to be a Super User!
you are welcome
Proud to be a Super User!
Hello @OverEgged,
Can you please try the following:
1. Create Measures
Sales Actual = CALCULATE(SUM(Table1[Value]), Table1[KPI] = "Sales", Table1[Category] = "Actual")
Sales Budget = CALCULATE(SUM(Table1[Value]), Table1[KPI] = "Sales", Table1[Category] = "Budget")
Cost1 Actual = CALCULATE(SUM(Table1[Value]), Table1[KPI] = "Cost1", Table1[Category] = "Actual")
Cost1 Budget = CALCULATE(SUM(Table1[Value]), Table1[KPI] = "Cost1", Table1[Category] = "Budget")
Ratio Measures
Measure1 Actual = DIVIDE([Cost1 Actual], [Sales Actual])
Measure1 Budget = DIVIDE([Cost1 Budget], [Sales Budget])
Variance Measures
Measure1 Variance = [Measure1 Actual] - [Measure1 Budget]
2. Create KPI Table
KPI Table =
DATATABLE(
"KPI", STRING,
"Actual", DOUBLE,
"Budget", DOUBLE,
"Variance", DOUBLE,
{
{"Measure1", [Measure1 Actual], [Measure1 Budget], [Measure1 Variance]},
{"Measure2", [Measure2 Actual], [Measure2 Budget], [Measure2 Variance]}
}
)
Thanks @Sahir_Maharaj , I'm trying your solution, but I'm getting the error: "The tuple at index '1' from the table definition of the DATATABLE function does not have a constant expression in the column at index '2'."
I'm trying to troubleshoot this now
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
88 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |