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
Anonymous
Not applicable

How to project the correct totals for a Table Visual in PowerBi from a big dataset

Hi all,

 

I am currently working on a Physical to Financial PowerBi Analytic where the data sets contains the data from every job on the field (I work in a construction engineering company) the data shows the phase (Category) and quantities (units) reported vs estimated, costs, amont others.

When I connect the SQL DB to my dashboard I want to show the values for every job. Meaning if I filter for example for job '948' it will show the data for that job. However. in the totals for that visual it is showing the weight percentage of that job over the total of all jobs combined. See below how it projects the numbers 5.12%, 5.36% % 4.24% which is the weight of that job over all in the databse (there are many). 

triniaguirreg_0-1633360526000.png

 

What I want it to show is the total weight for the 100% of that specific job and not overall. It should look like 100% for the Total Category Weighting totals, and 80% aprox for Physical POC and 70% aprox for Financial POC. Is there a way to project the totals for the 948 job in this visual and not for all the jobs combined???? 

 

 

 

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@Anonymous 

Try add a condition to the 3rd column:

 

Phase-job weight  = Calculate(divide([EstimatedCAC_[Job&Phase],[JobTotal]), Filter('JCCosttoCompleteTable', [Job]=Earlier[Job]))

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
V-pazhen-msft
Community Support
Community Support

@Anonymous 

Try add a condition to the 3rd column:

 

Phase-job weight  = Calculate(divide([EstimatedCAC_[Job&Phase],[JobTotal]), Filter('JCCosttoCompleteTable', [Job]=Earlier[Job]))

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

Division should be

divide(Sum(Table[A]), Sum(Table[B]))

 

Percent of Total- https://www.youtube.com/watch?v=uO3ojDPOpcg&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj&index=7

Anonymous
Not applicable

The way the layout of the data (headers is the following):

triniaguirreg_1-1633366275114.png

The columns below come after the Original Estimated Cost column at the top. These 3, are calculated columns where I break down the category values by job and by phase. (Since my data includes the whole listing of jobs, phases and numbers (cost and units) I plugged three measures to get the total estimated by job and phase. and then job total to get the weight of that item for the total job. (Estimated CAC/JobTotal)

 

The three measures for the calculated columns are:

 

1) Estimted Cost  at Completion (by job and by phase) triniaguirreg_5-1633366572919.png

2) The estimated cost at completion total amount for the full job

triniaguirreg_4-1633366534043.png

3) Lately the division of 1) and 3) to get the weight for that phase over the job. 

triniaguirreg_6-1633366675591.png

Results:

triniaguirreg_2-1633366302567.png

 

So the three values I am trying to get to show the totals correct are Total Category Weighting , W Physical POC & W financial POC. These are using the calculated columns and in the totals its projecting a 5%. This is the percentage of that job over all the jobs from my dataset but I need it to project the totals for only and exclusively that job.

 

Thanks!!!! 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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