Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 tadabase format
The output in matrix table and the result that I need to achieve:
Thank you very much for your support
Reub
Solved! Go to Solution.
done!!!
New2 = DIVIDE ( CALCULATE ( SUMX ( VALUES ( Database[Location] ); [YTD Productivity] * [Total Production] ); Database[Location] = "Barcelona" || Database[Location] = "Madrid" ); CALCULATE ( SUM ( Database[Production] ); Database[Location] = "Barcelona" || Database[Location] = "Madrid" ) )
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!!!
done!!!
New2 = DIVIDE ( CALCULATE ( SUMX ( VALUES ( Database[Location] ); [YTD Productivity] * [Total Production] ); Database[Location] = "Barcelona" || Database[Location] = "Madrid" ); CALCULATE ( SUM ( Database[Production] ); Database[Location] = "Barcelona" || Database[Location] = "Madrid" ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |