New business

[Anonymous]

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,

[MFelix]

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]

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...)

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may try a similar way as shown here.

[Anonymous]

I try SUMMARIZE function (I think I never did it before) and it's working!

Thank you!

[Anonymous]

Thank you,

I think it's not actually the same problem.

I got 3 tables (salers, customers and orders).

Anyway, I shall think about your answer. Thanx.

I let you know.