Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Keep returning text in my visualization rows

Hi,

 

I keep pulling up text in my matrix for this dax measure Average Spend =
VAR CohortStartMonth = SELECTEDVALUE('AR Invoice'[Cohort])
VAR CurrentMonth = EOMONTH(CohortStartMonth, SELECTEDVALUE('(customer) Period Out'[Value]))
VAR TransactionsInCohortPeriod =
FILTER(
'AR Invoice',
RELATED('Rolling Calendar'[End of Month]) = CurrentMonth
)
VAR Invoices =
SUMMARIZE(
TransactionsInCohortPeriod,
'AR Invoice'[invoice_id],
"Invoice Total", SUM('AR Invoice'[invoice_amount])
)
RETURN
AVERAGEX(Invoices, [Invoice Total])  I am also using a 

(customer) Period Out = GENERATESERIES(0,DISTINCTCOUNT('Rolling Calendar'[End of Month])-1) in a column and AR invoice dates hope someone can help me figure out the problem. I'm also using a slicer 
(customer) Measure Selection =
DATATABLE(
    "Measure Name", STRING,
    "Sort", INTEGER,
    {
        {"Customers", 1},
        {"Customers Retained", 2},
        {"Average Spend", 3}
    })
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , if you are looking for Cohort analysis refer

     

    https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/ba-p/1393410

    Power BI Cohort Analysis, Customer Retention %- https://youtu.be/qY1SDF1cwsg

     

    The information you have provided is not making the problem clear to me. Can you please explain with an example.
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Amitchandak,

      I implemented your retention solution instead and it works great but I have a question. My results are showing 100% everywhere and I'm not sure where I went wrong. I am pulling from my ar invoice table the customer id's 

       

      Preview
      Retain % = CALCULATE(divide(DISTINCTCOUNT('AR Invoice'[customer_id]),CALCULATE(DISTINCTCOUNT('AR Invoice'[customer_id]),ALLSELECTED('Customer Age Bucket') , 'Customer Age Bucket'[Age] =1)) , 'Customer Age Bucket'[Age] >1)  Where do you think I went wrong?
       
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Thanks amitchandak  for the quick reply. Allow me to suggest other ideas:
    Have you tried creating the measures separately to see if the output is correct? Such as:

    _a =
DISTINCTCOUNT ( 'AR Invoice'[customer_id] )
    _b=
        CALCULATE (
            DISTINCTCOUNT ( 'AR Invoice'[customer_id] ),
            ALLSELECTED ( 'Customer Age Bucket' ),
            'Customer Age Bucket'[Age] = 1
        )
    _c =
DIVIDE (_a,_b)
    Retain % =
CALCULATE (_c,
    'Customer Age Bucket'[Age] > 1
)

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    What exactly are you trying to do?  Share some data, explain the question in simple language and show the expected result.  Share data in a format that can be pasted in an MS Excel file.