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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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? (Yes, its FREE!)
➤ 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.