Forum Discussion

OverEgged's avatar
OverEgged
Regular Visitor
2 years ago
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...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

     

    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

     

     

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
2 years ago

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's avatar
OverEgged
Regular Visitor
2 years ago

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's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    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.