Forum Discussion
Calculating Weighted average from transactional data
Hi all,
This is a first for me. I have looked at the other solutions and still can't figure it out.
I have transactiona data like below (note there are 20+ variables). I need to calculate the weighted average of the balance and i want to show it for different segment types if possible... ie. by class or by month & class and so forth
| ID | Class | Balance | Date |
| 1 | mm | 733.66 | 2021-11-02 |
| 2 | h | 1742.48 | 2021-11-02 |
| 3 | ll | 726.65 | 2021-11-02 |
| 4 | lr | 680.05 | 2021-11-02 |
| 5 | ll | 1405.36 | 2021-11-02 |
| 6 | mr | 904.08 | 2021-11-02 |
| 7 | ml | 387.54 | 2021-11-02 |
| 8 | h | 549.25 | 2021-11-02 |
| 9 | hl | 1124.7 | 2021-11-02 |
| 10 | hr | 444.31 | 2021-11-02 |
What would be the best way to approach this ? Appreciate any direction on this frequently discussed topic
To calculate the weighted average according to different segment types, you may need to create multiple measures.
The measure I created is calculated according to class.
WA Sales by Product = VAR AnnualSales = CALCULATE( SUM('Table (2)'[Balance]), ALL('Table (2)'[Class]) ) VAR SummarisedTable = ADDCOLUMNS( SUMMARIZE( 'Table (2)','Table (2)'[Class],'Table (2)'[Date].[Year] ), "SalesWt", SUM('Table (2)'[Balance]) / AnnualSales, "Sales", SUM('Table (2)'[Balance]) ) RETURN SUMX( SummarisedTable, [SalesWt] * [Sales] )
2 Replies
NOVICE02 , What is the expected value.
You can try a measure like
divide(sum(Table[Balance]), count(Table[Balance]))
To calculate the weighted average according to different segment types, you may need to create multiple measures.
The measure I created is calculated according to class.
WA Sales by Product = VAR AnnualSales = CALCULATE( SUM('Table (2)'[Balance]), ALL('Table (2)'[Class]) ) VAR SummarisedTable = ADDCOLUMNS( SUMMARIZE( 'Table (2)','Table (2)'[Class],'Table (2)'[Date].[Year] ), "SalesWt", SUM('Table (2)'[Balance]) / AnnualSales, "Sales", SUM('Table (2)'[Balance]) ) RETURN SUMX( SummarisedTable, [SalesWt] * [Sales] )
