Forum Discussion
Solved
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 t...
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
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