cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
guilriou Frequent Visitor
Frequent Visitor

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

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

 

CustomerMonthSustainability Score
Client 12/1/20192
Client 22/1/20194
Client 23/1/20194
Client 32/1/20193
Client 44/1/20193
Client 13/1/20193
Client 33/1/20193
Client 54/1/20195
Client 55/1/20196
Client 45/1/20190

 

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!

2 REPLIES 2
Community Support Team
Community Support Team

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

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 )

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
guilriou Frequent Visitor
Frequent Visitor

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

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-101234
0    48464603813
1   2160110963814352308
2  4230495627431011109 
3 1311798725512725273 
4 924785445916599  
5125992311104   
6    1211   

For reference I attach a dummy sample of the data I'm using here

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 379 members 3,740 guests
Please welcome our newest community members: