Forum Discussion

sjors_bi's avatar
sjors_bi
New Member
1 year ago
Solved

conversion/penetration/retention/churn report

 

I've got a database on a daily level.

I want to calculate the conversion/penetration/retention/churn per month per product group.

I finally thought this would be easy with the new visual calculations, but this shows no totals for conversion and churn.

 

the visual calculations:

conversion = IF( [cpr stems ly] = 0 , [cpr stems ty])
penetration = IF( [cpr stems ly] > 0 && [change] >0 , [cpr stems ty]- [cpr stems ly])
 
retention = IF( [change] <0  , [cpr stems ty], [cpr stems ly])
churn = IF( [change] < 0, [change])
 
I get it why this doesnt show up in the total, because at the total level the validation is correct.
But is there a workaround to just sum up the rows?
 
In total we can have the same sales, but when a customer switches product group I want it to show up as conversion in the first and as churn on the second group. So I really want it calculated row by row 
 
Thanks in advance
 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from lbendlin  please allow me to provide another insight:

    Hi, sjors_bi 
    Thanks for reaching out to the Microsoft fabric community forum.

    1.Based on your description of the issue, it is because, in visual object calculations, similar to measures, an aggregate value is required as the output result. These calculations depend on the context within the data model, meaning they vary based on different rows. Therefore, in the total section, it also determines that the total value is non-zero, resulting in the default output of the conditional statement being false=blank, as shown in the image below:

    For further details, please refer to:
    Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

    Tutorial: Create your own measures in Power BI Desktop - Power BI | Microsoft Learn
     

    2.Correcting this result is straightforward, but it requires referencing a table, which is not supported in visual object calculations. Therefore, I recommend using a measure to replace your visual object calculation:

    conversion = 
CALCULATE([cpr stems ty],FILTER('Table',[cpr stems ly] = 0 ))
    penetration = 
 CALCULATE([cpr stems ty]- [cpr stems ly],FILTER('Table',[cpr stems ly] > 0 && [change] >0))

    retention = IF( [change] <0  , [cpr stems ty], [cpr stems ly])
    churn = CALCULATE([change],FILTER('Table',[change] < 0))

    3.Here's my final result, which I hope meets your requirements.

     

    Please find the attached pbix relevant to the case.

     

    Best Regards,

    Leroy Lu

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

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    I assume you are aware of this DAX Pattern New and returning customers – DAX Patterns

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

     

     

    • sjors_bi's avatar
      sjors_bi
      New Member
      1 year ago

      Thanks for the response! I'll first look at the dax pattern.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for the reply from lbendlin  please allow me to provide another insight:

        Hi, sjors_bi 
        Thanks for reaching out to the Microsoft fabric community forum.

        1.Based on your description of the issue, it is because, in visual object calculations, similar to measures, an aggregate value is required as the output result. These calculations depend on the context within the data model, meaning they vary based on different rows. Therefore, in the total section, it also determines that the total value is non-zero, resulting in the default output of the conditional statement being false=blank, as shown in the image below:

        For further details, please refer to:
        Using visual calculations in Power BI Desktop - Power BI | Microsoft Learn

        Tutorial: Create your own measures in Power BI Desktop - Power BI | Microsoft Learn
         

        2.Correcting this result is straightforward, but it requires referencing a table, which is not supported in visual object calculations. Therefore, I recommend using a measure to replace your visual object calculation:

        conversion = 
CALCULATE([cpr stems ty],FILTER('Table',[cpr stems ly] = 0 ))
        penetration = 
 CALCULATE([cpr stems ty]- [cpr stems ly],FILTER('Table',[cpr stems ly] > 0 && [change] >0))

        retention = IF( [change] <0  , [cpr stems ty], [cpr stems ly])
        churn = CALCULATE([change],FILTER('Table',[change] < 0))

        3.Here's my final result, which I hope meets your requirements.

         

        Please find the attached pbix relevant to the case.

         

        Best Regards,

        Leroy Lu

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