How to calculate Sum/Totals with an if-condition based on measure?

[ratri]

Hi, 

hope you can help me out. I have following data: 
Customer Table 

CRM Table 

Turnover Table 

the CRM and Turnover are linked through Customer ID to the Customer Table 

Now I need to calculate the Turnover for customer were not visited this year. 

I already calcualted the

1. number of customers

DISTINCTCOUNT(Personenkonto_1[CustomerID])

2. number of visits in a year for the customer 

CALCULATE([Anzahl Vorgänge];FILTER('Aktivität CRM_9';'Aktivität CRM_9'[Bezeichnung]="Visit");all(Team_6[Team]))

3. Sales for the customer in a year 

CALCULATE(sum(Mengendaten[Sales]);USERELATIONSHIP(Bereichsbezeichnung[Bereich];Mengendaten[Bereichsbezeichnung]))

So how can i build up a formula ? I tried with an if-function: if(number of visits<=0;Sales;0) --> it turns the correct value, but the Total Sum field in the table is showing 0, But i need the total Sales value for those customers that were not visited yet. 

any idea? 

Regards, 

Anastasija

[v-diye-msft]

Hi @ratri 

You might consider creating pbix file that will contain some sample data, upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[ratri]

Hi, 

i created an example. Hope you can download it in DropBox: 

https://www.dropbox.com/s/2oooowkph8q5rld/Example%20issue.pbix?dl=0 

Ratri

[v-diye-msft · Accepted solution]

Hi @ratri 

Please add a new measure instead:

Measure = SUMX(Customer,[Calculate Sales for no visits])

 

[amitchandak]

Please refer hasonevalue

https://docs.microsoft.com/en-us/dax/hasonevalue-function-dax

[ratri]

Hi, 

hasonavlue referst to column. But i have no column that is indicating the number of visits. i have only a measure and with measure HASONVALUE is not working. 

BG,

Ratri