Forum Discussion

DiKi-I's avatar
DiKi-I
Post Partisan
1 year ago
Solved

Need help with data modelling

 

Hi ,
I need help with one of the the requirement. I have a csutomer churn table with csutomer and churn status and an invoice table.
For new and returning customer there is not any issue since they fall in the same fiscal year and can be easily sliced. 
The issue is with the lost customer. If a customer is lost in 2022 then it will have revenue in 2021 and not in 2022. But the client wants to see the figures as lost revenue in 2022 and not 2021. Could you please help how i can do this in power bi.

I have attached the sample pbix file with data. Please let me know for any questions.

 

https://drive.google.com/file/d/1ZdbyUWeARhVWBV8ncd47izKbhnR5MKY-/view?usp=drive_link

 

I have to show lost /new/returning in the bar chart as legend and the revenue for the different fiscal year. If a customer is lost in 2022 , in 2022 it should should show lost revenue(2021 revenue)

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hello DiKi-I 

    You can refer to the following solution.

    1.Create a new table. the table has no relationship with other tables.

     

    ChurnType = VALUES(CustomerChurn[Churn])

     

    2.Create a measure

     

    MEASURE =
SWITCH (
    SELECTEDVALUE ( 'ChurnType'[Churn] ),
    "Lost",
        VAR a =
            CALCULATETABLE (
                VALUES ( CustomerChurn[ContactID] ),
                ALLSELECTED ( CustomerChurn[ContactID] ),
                CustomerChurn[FiscalYear] = SELECTEDVALUE ( 'Date'[FiscalYear] ),
                CustomerChurn[Churn] = "Lost"
            )
        RETURN
            CALCULATE (
                SUM ( Invoice[Amount] ),
                ALL ( Invoice ),
                Invoice[ContactID] IN a,
                Invoice[FiscalYear]
                    = SELECTEDVALUE ( 'Date'[FiscalYear] ) - 1
            ),
    CALCULATE (
        SUM ( Invoice[Amount] ),
        CustomerChurn[Churn] IN VALUES ( 'ChurnType'[Churn] )
    )
)

     

    3.Then create a bar chart, and put the following field to the visual.

     

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello DiKi-I 

    You can refer to the following solution.

    1.Create a new table. the table has no relationship with other tables.

     

    ChurnType = VALUES(CustomerChurn[Churn])

     

    2.Create a measure

     

    MEASURE =
SWITCH (
    SELECTEDVALUE ( 'ChurnType'[Churn] ),
    "Lost",
        VAR a =
            CALCULATETABLE (
                VALUES ( CustomerChurn[ContactID] ),
                ALLSELECTED ( CustomerChurn[ContactID] ),
                CustomerChurn[FiscalYear] = SELECTEDVALUE ( 'Date'[FiscalYear] ),
                CustomerChurn[Churn] = "Lost"
            )
        RETURN
            CALCULATE (
                SUM ( Invoice[Amount] ),
                ALL ( Invoice ),
                Invoice[ContactID] IN a,
                Invoice[FiscalYear]
                    = SELECTEDVALUE ( 'Date'[FiscalYear] ) - 1
            ),
    CALCULATE (
        SUM ( Invoice[Amount] ),
        CustomerChurn[Churn] IN VALUES ( 'ChurnType'[Churn] )
    )
)

     

    3.Then create a bar chart, and put the following field to the visual.

     

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.