Build a visual showing evolution of a parameter with specific insights on the evolution

[guilriou]

Hi everyone,

I would like to ask for some help regarding a report I've been working for some time and can't seem to figure out the solution.

We have a database of customers that are attributed a number from 0 to 7 that defines their sustainability "score" and that we track every month. The database looks like this and links to another with more specific information allowing us to trace the customer

Customer	Month	Sustainability Score
Client 1	2/1/2019	2
Client 2	2/1/2019	4
Client 2	3/1/2019	4
Client 3	2/1/2019	3
Client 4	4/1/2019	3
Client 1	3/1/2019	3
Client 3	3/1/2019	3
Client 5	4/1/2019	5
Client 5	5/1/2019	6
Client 4	5/1/2019	0

The report so far visualizing every month the proportion of customer of level 0,1....7 is easy. But we want to be able to be more specific and get more insight on how customer evolution happened from a month to another. 

For example: if in Month 1 we have 13,000 people in group level 0, 12,000 in group level 1 and in Month 2, we have 14,000 in group level 0, 10,300 in group level 1, what's the proportion from group 1 who downgraded to level 0, and how many from group 0 who migrated to group 1, how many are new ones etc.

This is to enable us to identify the customers that are improving and those that are degrading so we can conduct the right action on the right target.

I've checked the solution here to get the "first value" and "last value" when two specific months are selected here

but I can only exploit it through a table and it requires additional manual extraction and manipulation on excel to get what I want.

I then tried this one here but it didn't work at all.

So ideally from a selected month to another selected month, knowing how each group evolved (x% of group 0 leveled up to group 1, y% of group 1 leveled down to group 0 etc) would nail it. 

Anyone has a clue on how to proceed ?

It's the first time I'm using this forum so if I haven't made myself clear or specific enough please do let me know.

Thank you much in advance!

[v-frfei-msft]

Hi @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 )

[guilriou]

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