Forum Discussion
Build a visual showing evolution of a parameter with specific insights on the evolution
Community SupportHi guilriou ,
One sample for your reference. If it doesn't meet your requirement, kindly share your excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
up =
VAR sc =
MAX ( Table1[Sustainability Score] ) + 1
VAR ca =
CALCULATE (
COUNT ( Table1[Customer] ),
FILTER (
Table1,
Table1[Month] >= SELECTEDVALUE ( f1[Month] )
&& Table1[Month] <= SELECTEDVALUE ( f2[Month] )
)
)
VAR ca2 =
CALCULATE (
COUNT ( Table1[Customer] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Month] >= SELECTEDVALUE ( f1[Month] )
&& Table1[Month] <= SELECTEDVALUE ( f2[Month] )
&& Table1[Sustainability Score] = sc
)
)
RETURN
DIVIDE ( ca2 - ca, ca )
down =
VAR sc =
MAX ( Table1[Sustainability Score] ) - 1
VAR ca =
CALCULATE (
COUNT ( Table1[Customer] ),
FILTER (
Table1,
Table1[Month] >= SELECTEDVALUE ( f1[Month] )
&& Table1[Month] <= SELECTEDVALUE ( f2[Month] )
)
)
VAR ca2 =
CALCULATE (
COUNT ( Table1[Customer] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Month] >= SELECTEDVALUE ( f1[Month] )
&& Table1[Month] <= SELECTEDVALUE ( f2[Month] )
&& Table1[Sustainability Score] = sc
)
)
RETURN
DIVIDE ( ca - ca2, ca2 )
Thanks for the reply v-frfei-msft ,
I implemented it in my report but the results do not match. The score 0 people usually don't upgrade, they remain steady or they lapse away, and applying your measures I get 80% in the up section which doesn't fit.
Starting from this base though, would it be possible to have a set of measures telling us:
From X month to Y month xxxx clients with score 0 upgraded to score 1
From X month to Y month xxxxx clients with score 1 downgraded to score 0
From X month to Y month xxx clients with score 1 upgraded to score 3 and so on..
Here's a sample of what I would like to be able to see on POWER BI: here this tells us that from month 1 to month 2, 4846 customers with score 0 remained as is, while a total of 115 upgraded to higher levels, the distribution being clearly outlined.
Having these data calculated based on the months selected, and being able to use these to identify the customer ID for each segment is what I am trying to get.
| Score Change | |||||||||
| Score starting month | -5 | -3 | -2 | -1 | 0 | 1 | 2 | 3 | 4 |
| 0 | 4846 | 4 | 60 | 38 | 13 | ||||
| 1 | 2160 | 11096 | 381 | 435 | 230 | 8 | |||
| 2 | 4 | 2304 | 9562 | 743 | 1011 | 109 | |||
| 3 | 1 | 31 | 1798 | 7255 | 1272 | 527 | 3 | ||
| 4 | 9 | 24 | 785 | 4459 | 1659 | 9 | |||
| 5 | 1 | 2 | 5 | 99 | 2311 | 104 | |||
| 6 | 121 | 1 |
For reference I attach a dummy sample of the data I'm using here