weighted averages

3 Topics

Help with weighted sum calculation
Hi all, I am trying to do the weighted average calculations as below. However, struggling to replicate the calculations. My tables in the data models. My power bi report looks like the below. The Target (weighted sum) should be calculated using the weighted man hours spent in each division across the selected locations. Please see the calculation below. Example calculation of Target Weighted Sum: Thank you
gauthamboppana
6 years ago Place DAX Commands and Tips
991Views
0likes
1Comment
Weighted average of group minimums
Hi Folks! I have a following problem. Let say I have a tabular model with 2 tables: the first one has 3 columns: ReportingDate, Company, Price the second has 2 columns: Company, Weight I would like to define a measure which calculates the weighted average of the companies price minimums (i have relation between the tables, however i don't know if it is needed or not). I tried with this code DEFINE MEASURE 'Fact'[weighted_avg_of_minimums] = SUMX ( VALUES ( 'Fact'[Company] ), CALCULATE ( LOOKUPVALUE('Weights_to_Companies'[Weight], [Company], CURRENTGROUP() ) * MINA ( 'Fact'[Price] ) ) ) EVALUATE ( SUMMARIZE ( FILTER ( 'Fact', 'Fact'[ReportingDate] = DATE ( 2019, 2, 25 ) ) ), 'Fact'[Company], "Weighted minimums", CALCULATE ( 'Fact'[weighted_avg_of_minimums] ) ) ) of course the measure is not working with the CURRENCTGROUP() function. At this point i ran out of ideas.. I appreciate all your help 🙂 regards, Gallusz
Solved
abaligetig
6 years ago Place DAX Commands and Tips
1.2KViews
1like
3Comments
Matrix weighted averages in subtotals and total
Hi, Is there someone who can help me by the following. I have the folowing table: Table: Salesman Region Salesman Sales Budget Margin Index Margin Budget A A1 1.000.000,00 50 500.000,00 A A2 2.000.000,00 60 1.200.000,00 A A3 2.500.000,00 55 1.375.000,00 B B1 2.000.000,00 60 1.200.000,00 B B2 4.000.000,00 45 1.800.000,00 B B3 3.000.000,00 55 1.650.000,00 C C1 1.500.000,00 55 825.000,00 C C2 2.000.000,00 50 1.000.000,00 C C3 6.000.000,00 40 2.400.000,00 Total 24.000.000,00 11.950.000,00 Region A AVG Margin Index 55,91 Region B AVG Margin Index 51,67 Region C AVG Margin Index 44,47 Total AVG Margin Index 49,79 And I want to put it in a matrix like the one below: Subtotals by region and a grand total 1) Sales Budget is a SUM 2) Margin Budget is a Sum 3) Margin Index is a weighted AVERAGE (this is the one I am fighting with !!!!) Requested Visual Power BI Matrix Region Salesman Sales Budget Margin Index Margin Budget A 5.500.000,00 55,91 3.075.000,00 A1 1.000.000,00 50,00 500.000,00 A2 2.000.000,00 60,00 1.200.000,00 A3 2.500.000,00 55,00 1.375.000,00 B 9.000.000,00 51,67 4.650.000,00 B1 2.000.000,00 60,00 1.200.000,00 B2 4.000.000,00 45,00 1.800.000,00 B3 3.000.000,00 55,00 1.650.000,00 C 9.500.000,00 44,47 4.225.000,00 C1 1.500.000,00 55,00 825.000,00 C2 2.000.000,00 50,00 1.000.000,00 C3 6.000.000,00 40,00 2.400.000,00 Total 24.000.000,00 49,79 11.950.000,00
ProveluNL
6 years ago Place DAX Commands and Tips
1.8KViews
0likes
1Comment