Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Hello Good afternoon, I don't know if you could find the error, however I think I could make use of the join and row function to create the table with the KPI's my solution would be:
Table_KPI=
UNION (
ROW("Aggregation","KPI_1","value",CALCULATE(Expression)),
ROW("Aggregation","KPI_2","value",CALCULATE(Expression))
)
This problem caught my attention since I am also trying to get this out and, after almost 8 hours, I found a solution that in my opinion is not very efficient but intuitive. Please let me know if this worked.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
109 | |
94 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |