cancel
Showing results 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

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).

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
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.

3 REPLIES 3
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.

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

Anonymous
Not applicable

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

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)

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

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

Results:

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!!!!