Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

New business

Hi everyone,

I got one more problem (this is endless!?)

I would like to calculate the new business. This would be the customers, for each month, who subscribe something (with invoice).

 

I got one table with my customers (with a calculated measure), one table with the related salers (those two tables are linked) and tables with invoices (which is linked with the customers)

 

I have something like that (X is my boolean newbusiness or not/Nb is only a distinctcounter to get the number of new customers) :

 

1. X is not a sum -> How would I do to have 43 (instead of 1)?

2. This is not working when I choose many month -> All the customers appears with 0 or 1

3. I would have it by salers -> How would I do? Now, I actually have all the amounts, not only the new business.

 

Thank you for all,

 

Need Help
Show and Tell

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi Anonymous,

     

    You question is not very clear can you please see this post regarding How to Get Your Question Answered Quickly:

    https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of Greg_Deckler).

     

    If you can share some sample data and outcome it's easier to help you, it can be a mockup of your data and not actual data itself, if the model is to complex to make a mockup you can also share it trhough private message instead.

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Ok, sorry

      I found out something right but that's not enough.

       

      So,

       

      On table with my customer :

      Ref             Name                     Saler

      CRM-01     CompanyX              SL-01

      CRM-02     CompanyY              SL-02

      ...

       

      One table with my salers

      Ref            Name

      SL-01         MisterX

      SL-02         MissY

      ...

       

      One table SalesOrders

      Ref              Date                  AAAA_MM    Customer    Amount

      SO-01         01/05/2018       2018_05        CRM-01       1000€

      SO-02         05/10/2018       2018_10        CRM-02       500€

      ...

       

      So, I would numerate the customer (and amount) who did not have any salesorder for the 12 last past months but have an amount on the selected month.

      I got a measure ont my table salers [NewBusiness]-> calculate(sum(SalesOrder[Amount]);FILTER(Customers;[NBMeasure]=1)

      with [NBMeasure] (table customer)=IF(AND([CA_13mois]=0;sum(Salesorders[Amount])<>0);1;"")

      and [CA_13mois] is the amount for the last past 12months.

      I got one more measure to calculate the Ratio NB/total ->
      DIVIDE([NewBusiness];sum(SalesOrders[Amount]))
       
      I get the good result for each month. The total NewBusiness is right cause this is the result for December :
      AAAA_MM   NewBusiness        Ratio
      2018_01       X01                X01/(totalamount on January)
      2018_02       X02                X02/(totalamount on February)
      2018_03       X03                X03/(totalamount on March)
      2018_04       X04                X04/(totalamount on April)
      2018_05       X05                X05/(totalamount on May)
      2018_06       X06                X06/(totalamount on June)
      2018_07       X07                X07/(totalamount on July)
      2018_08       X08                X08/(totalamount on August)
      2018_09       X09                X09/(totalamount on September)
      2018_10       X10                X10/(totalamount on October)
      2018_11       X11                X11/(totalamount on November)
      2018_12       X12                X12/(totalamount on December)
      TOTAL          X12                (X01+X02+X03+...)/(totalamount complete year)
       
      My final ratio is right, not my total new business (this is not what I exepect).
      I would have (X01+X02+X03...)