Creating a new table with measures

[OverEgged]

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

[ryan_mayu]

could you pls provide some sample data and expected output?

[OverEgged]

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

[ryan_mayu · Accepted solution]

@OverEgged 

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

[OverEgged]

@ryan_mayu perfect! Much appreciated!

[ryan_mayu]

you are welcome

[Sahir_Maharaj]

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]}
    }
)

[OverEgged]

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

[Syndicate_Admin]

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.