cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calulations with table values

Hi experts!
After doing lot of research in this forum and with the help of some of you, I have built a data table (see attached the pbix file);which shows the productivity in % for diferent machines and differente locations. But this brings me to a final step that Im not able to solve it by myself.
What I am trying to do is to take out from the table below, just the result from Madrid (70,89%) and Barcelona (83,92%) and consolidate both results weighting them according to "total production" measure.

( 70,89% x 52.550 + 83,92 x 135.100 ) / ( 52.550 + 135.100 ) = 80,27%

Here are my measures:

```Total Production = sum(Database[Production])

Total Working Hours = sum(Database[Working Hours])

TotalExpectedProduction = sum(Database[Expected Production])

Avexpectedprod = divide(sumx(Database;[TotalExpectedProduction]*[Total Working Hours]);sum(Database[Working Hours]))

YTD Productivity =
CALCULATE (
DIVIDE (
SUMX (
SUMMARIZE (
Database;
Database[Matchine];
"AA"; [Total Production] / ( [Total Working Hours] * [Avexpectedprod] )
);
[AA] * [Total Working Hours]
);
[Total Working Hours];
0
);
DATESYTD ( Calendar[Date]; "30/06" );
FILTER (
ALL ( 'Calendar' );
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
&& 'Calendar'[Fiscal Year] = MAX ( 'Calendar'[Fiscal Year] )
)
)```

The output in matrix table and the result that I need to achieve:

Thank you very much for your support

Reub

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Calulations with table values

done!!!

```New2 =
DIVIDE (
CALCULATE (
SUMX ( VALUES ( Database[Location] ); [YTD Productivity] * [Total Production] );
Database[Location] = "Barcelona"
);
CALCULATE (
SUM ( Database[Production] );
Database[Location] = "Barcelona"
)
)```
2 REPLIES 2
Regular Visitor

## Re: Calulations with table values

First step done!!

`New = divide(sumx(values(Database[Location]); [YTD Productivity]*[Total Production]);[Total Production])`

Thanks to @MattAllington that published this post https://community.powerbi.com/t5/Desktop/Subtotaling-with-SUMX-and-Summarize/td-p/216724

Now I have to figure it out how to filter "Madrid" and "Barcelona" in the measure (not with filter panel)

Thank you!!!

Regular Visitor

## Re: Calulations with table values

done!!!

```New2 =
DIVIDE (
CALCULATE (
SUMX ( VALUES ( Database[Location] ); [YTD Productivity] * [Total Production] );
Database[Location] = "Barcelona"
);
CALCULATE (
SUM ( Database[Production] );
Database[Location] = "Barcelona"
)
)```

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 99 members 1,422 guests
Recent signins: