Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ratri
Frequent Visitor

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

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

1 ACCEPTED SOLUTION

Hi @ratri 

 

Please add a new measure instead:

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

0006.PNG 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

5 REPLIES 5
v-diye-msft
Community Support
Community Support

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Hi, 

 

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

 

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

 

Ratri

Hi @ratri 

 

Please add a new measure instead:

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

0006.PNG 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
amitchandak
Super User
Super User

Please refer hasonevalue

 

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.