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
Tom_Y
Advocate II
Advocate II

Single DAX Measure to calculate Each project

Hi all,

 

All demo here. I'm looking for a more dynamic way to calculate a total for all projects.

 

I have 3 projects, Project1 ,2,3, each with separate table and "measures", say Apple, Orange, Banana..., and then a column "Count", and then date etc.

Another table "Carbon Saving" telling how one Apple/ Orange/ Banana is going to save the world.

 

I have the following DAX to sum the CO2 saved by Project1, and similar Dax for Project 2 & 3.

P1_AnnualCO2Saving =
   SUMX(
      Project1,Project1[Count]*RELATED('Carbon Saving'[Annual (CO2)])
   )

 

And I also have a table "All Project" appending everything from P1 P2 and P3, and a DAX to get a total Savings from all projects.

 

All_AnnualCO2Saving =
   SUMX(
      'All Project','All Project'[Count]*RELATED('Carbon Saving'[Annual (CO2)])
   )

 

Finally I have a "Project Table" having all the name of each project.

 

I'm going to have 100 projects, and then having new projects every month. Is there anyway I can do something like above by just one dynamic DAX instead of copy and paste 100 times? Thanks in advance.

 

Tom_Y_0-1701515889859.png

 

3 REPLIES 3
Rice
Helper I
Helper I

Hello!

 

It would seem to me this is a bad layout for your tables. I'm uncertain as to the reason you decided to go this route.

 

I would have all projects in a single table ('Project Results'? or Existing 'All Projects') with an extra column (projectID) linked to the main Project Table that exists.

 

This would allow for less calculations and measures. You would then setup visuals which would filter the results based on active context.

 

Perhaps a demo file with mockup data would help everyone assist you more easily.

Thanks! and agree. Each project has their own source file and their own project manager (that's what I can't change). Yes, I have a "All project" table, bottom right corner, basically "appending all rows" from project 1,2,3. And then I added measures to calculate savings based on "All project". It works.

1. I just wonder if there's way like Referencing a "Project Table" describing all projects on hand, so "All project" can automatically extract data from individual tables and append, instead of hard coding "Append P1,P2 & P3".

2. There will be hundred of projects, I don't want to add it one by one or revise  the code every week...

Thank you!

You're much better off combing and keeping it in one table using PowerQuery (M - Mashup) and not segregating it out at all.

 

Depending on how your data is coming in, for instance SQL, you could simply setup a view to combine them all in at once and then select the view, then just edit the view in the future. No need to setup relationships and merging every time you add a project.

 

In general, data transformations should take place at the data source as much as possible.

 

Hope that helps!

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.