Solved: Need help in calculating new ,upgrade , downgrade ...

svinayagam1984 · ‎03-20-2021

Hi,
Can someone please help to calculate the NEW, Upgrade, Downgrade, and lost customers?
I was able to calculate New, Upgrade but struggling to get the correct number for downgrade and cancellation.

New - Accounts that are created in the current week and not available in the past week's data.
Lost - Account available in last week's data but not available in current week's data.
Upgrade and Downgrade = Current week MRR - prior week MRR.

I have attached my pbix file. PBIX File

A quick response on this is much appreciated.

Anonymous · ‎03-25-2021

HI @svinayagam1984,

You can refer to the following steps:

1. Add a calculated table to store different states and sorting orders and configure the 'sort by column' property.

Table =
DATATABLE (
    "Type", STRING,
    "Index", INTEGER,
    {
        { "New", 1 },
        { "Upgrade", 2 },
        { "Downgrade", 3 },
        { "Lost", 4 }
    }
)

2. Create a matrix visual with date table 'year', 'week rank', and new table 'type' filed. (type to row, 'year', 'week rank' to column and expand to bottom)

3. Write measure format to handle date and write switch function to switch between different type of status.

Measure =
SWITCH (
    SELECTEDVALUE ( 'Table'[Type] ),
    "New", [New],
    "Upgrade", [Upgrade],
    "Downgrade", [Downgrade],
    "Lost", [Lost Customer Sale],
    BLANK ()
)

For the wrong calculation with downgrade and cancellation, I think this should be related to your calculation groups. They are common multiple aggregate calculation issues, you need to drill to detail account level to calculate accurately amount of each account and summary these diffs.

Measure Totals, The Final Word

Notice: please not attach sensitive/real data in your sample file. You can do masks or remove them before share.

Regards,

Xiaoxin Sheng

View solution in original post

Anonymous · ‎03-25-2021

HI @svinayagam1984,

You can refer to the following steps:

1. Add a calculated table to store different states and sorting orders and configure the 'sort by column' property.

Table =
DATATABLE (
    "Type", STRING,
    "Index", INTEGER,
    {
        { "New", 1 },
        { "Upgrade", 2 },
        { "Downgrade", 3 },
        { "Lost", 4 }
    }
)

2. Create a matrix visual with date table 'year', 'week rank', and new table 'type' filed. (type to row, 'year', 'week rank' to column and expand to bottom)

3. Write measure format to handle date and write switch function to switch between different type of status.

Measure =
SWITCH (
    SELECTEDVALUE ( 'Table'[Type] ),
    "New", [New],
    "Upgrade", [Upgrade],
    "Downgrade", [Downgrade],
    "Lost", [Lost Customer Sale],
    BLANK ()
)

For the wrong calculation with downgrade and cancellation, I think this should be related to your calculation groups. They are common multiple aggregate calculation issues, you need to drill to detail account level to calculate accurately amount of each account and summary these diffs.

Measure Totals, The Final Word

Notice: please not attach sensitive/real data in your sample file. You can do masks or remove them before share.

Regards,

Xiaoxin Sheng

svinayagam1984 · ‎03-25-2021

Thanks a lot @Anonymous

svinayagam1984 · ‎03-23-2021

Thanks @Anonymous
But, As I am just started to learn Power BI I m not able to use this example to solve my issue.
Any other suggestions?

Anonymous · ‎03-24-2021

HI @svinayagam1984,

OK, can you please share some dummy data and expected results to test? I can't access the shared link and your file seems removed from sharing.
Regards,

Xiaoxin Sheng