Forum Discussion
Creating a new table with measures
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
pls try to create a table
and create three measures
Actual = IF(SELECTEDVALUE('Table (2)'[Column1])="KPI1", CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Actual"&&'Table'[Attribute]="Cost1"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Actual"&&'Table'[Attribute]="Sales1")),CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Actual"&&'Table'[Attribute]="Cost 2"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Actual"&&'Table'[Attribute]="Sales2")))Budget = IF(SELECTEDVALUE('Table (2)'[Column1])="KPI1", CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Budget"&&'Table'[Attribute]="Cost1"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Budget"&&'Table'[Attribute]="Sales1")),CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Budget"&&'Table'[Attribute]="Cost 2"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Budget"&&'Table'[Attribute]="Sales2")))variance = [Actual]-[Budget]pls see the attachment below
8 Replies
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.
could you pls provide some sample data and expected output?
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
Actual = IF(SELECTEDVALUE('Table (2)'[Column1])="KPI1", CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Actual"&&'Table'[Attribute]="Cost1"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Actual"&&'Table'[Attribute]="Sales1")),CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Actual"&&'Table'[Attribute]="Cost 2"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Actual"&&'Table'[Attribute]="Sales2")))Budget = IF(SELECTEDVALUE('Table (2)'[Column1])="KPI1", CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Budget"&&'Table'[Attribute]="Cost1"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Budget"&&'Table'[Attribute]="Sales1")),CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Budget"&&'Table'[Attribute]="Cost 2"))/CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Type]="Budget"&&'Table'[Attribute]="Sales2")))variance = [Actual]-[Budget]pls see the attachment below
