Forum Discussion
Create cumulative weighted average measure with changing weight
Resident RockstarHi avcr29 ,
I have a little confused about your scenario.
If I understand your scenario correctly that you weighted table is your sample data and your desired output is the third table in your post?
If it is, could you share your formula of the measure x so that I could understand your logic better.
weighted measure x=
VAR __CATEGORY_VALUES = VALUES('table'[Category])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE([measure x] * LASTNONBLANK('table'[weigth],1))
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(LASTNONBLANK('table'[weigth],1))
)
)
In addition, I cannot understand the logic why cumulative measure x for June of CC is 60%?
Best Regards,
Cherry
Hi v-piga-msft !
Thanks for your reply!
Actually, my sample data is this:
| Date | Month | Category | Type | Weight |
| 01/03/2019 | March | CM | Detractor | 0.4 |
| 01/06/2019 | June | CM | Promoter | 0.35 |
| 04/06/2019 | June | CB | Detractor | 0.2 |
| 01/03/2019 | March | CV | Promoter | 0.4 |
| 04/06/2019 | June | CV | Detractor | 0.35 |
| 01/03/2019 | March | CC | Promoter | 0.2 |
| 04/06/2019 | June | CC | Detractor | 0.1 |
| 01/03/2019 | March | CM | Promoter | 0.4 |
| 04/06/2019 | June | CM | Detractor | 0.35 |
| 02/03/2019 | March | CM | Promoter | 0.4 |
| 02/06/2019 | June | CM | Detractor | 0.35 |
| 06/06/2019 | June | CB | Promoter | 0.2 |
| 09/03/2019 | March | CV | Promoter | 0.4 |
| 05/06/2019 | June | CV | Promoter | 0.35 |
| 03/03/2019 | March | CC | Promoter | 0.2 |
| 09/06/2019 | June | CC | Promoter | 0.1 |
| 09/03/2019 | March | CM | Detractor | 0.4 |
| 05/06/2019 | June | CM | Promoter | 0.35 |
| 06/03/2019 | March | CM | Promoter | 0.4 |
| 07/06/2019 | June | CM | Promoter | 0.35 |
| 03/06/2019 | June | CB | Promoter | 0.2 |
| 09/03/2019 | March | CV | Promoter | 0.4 |
| 21/06/2019 | June | CV | Promoter | 0.35 |
| 22/03/2019 | March | CC | Promoter | 0.2 |
the formula of measure x is:
measure x=
CALCULATE((COUNTAX(FILTER ( table, table[Type] = "Promoter" ), table[Type] )/COUNTA(table[type]))-(COUNTAX(FILTER ( table, table[Type] = "Detractor" ), table[Type]) /COUNTA(table[Type])))
the weighted table is just for explanatory purposes (how the weight change by month) but it's not my sample data it's just one of the columns.
In addition, about your last question, june is that percent because that's the result if you apply the measure x since march till june.
category CC:
march 100.00%
june 60.00%
that's why in the third table (in my first post) when in the column "cumulative measure x" says that value, is because i want that the cumulated results by month (march, (march+june), (march+june+september), (march+june+september+december)) are the ones i want to be ponderated with the weights.
i hope i have resoult your questions, thanks for the help.