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

     

     

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

could you pls provide some sample data and expected output?

  • OverEgged's avatar
    OverEgged
    Regular Visitor
    2 years ago

    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's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      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

       

       

      • OverEgged's avatar
        OverEgged
        Regular Visitor
        2 years ago

        ryan_mayu perfect! Much appreciated!