Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
OverEgged
Regular Visitor

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

 

1 ACCEPTED SOLUTION

 

@OverEgged 

pls try to create a table

11.PNG

 

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]
 
12.PNG
 

 

pls see the attachment below

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
ryan_mayu
Super User
Super User

could you pls provide some sample data and expected output?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

@OverEgged 

pls try to create a table

11.PNG

 

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]
 
12.PNG
 

 

pls see the attachment below

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ryan_mayu perfect! Much appreciated!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sahir_Maharaj
Super User
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors