Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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]
)
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]
)
@NOVICE02 , What is the expected value.
You can try a measure like
divide(sum(Table[Balance]), count(Table[Balance]))
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
71 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |