countif a comparative condition is met

[Anonymous]

Hi everyone
I need your help with the following problem:
I have two tables, in one these the goals of visits by seller to their customers and in the other are the visits made daily, both related.
The idea is that the percentage of visit compliance never exceeds 100%, regardless of whether you have made more views than planned.
So, what we are looking for is a formula that counts the visits made but the result does not exceed the number of the goal that is in the other table.

I thank you for what you can help me

[amitchandak · Accepted solution]

Add these two columns in table 1

	visitaReales= countx(filter(table2,table1[vendedor]=table2[vendedor]),table2[ClienteCod])
	VisitaMax=if(table1[meta]<=table1[visitaReales],table1[meta],table1[visitaReales])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[v-yiruan-msft · Accepted solution]

Hi @Anonymous，

According to the information in your case, I just create one sample PBIX file. You can create one measure just like in sample file:

1. Get the current customer
2. Calculate numbers of current customer visit
3. If the number of user visits is greater than the goal number , it is counted as the goal number. Otherwise it is counted as the actual number of visits. Divide the number of final visits by the goal number to calculate the percentage of visits  

maxVisit = VAR c =

    MAX ( 'Visits'[Customer] )

VAR s =

    COUNTAX ( 'Visits', 'Visits'[Customer] = c )

VAR vgoal =

    CALCULATE (

        MAX ( 'VisitGoal'[Goals] ),

        FILTER ( 'VisitGoal', 'VisitGoal'[Customer] = c )

    )

RETURN

   DIVIDE( IF ( s < vgoal, s, vgoal ),vgoal)

Best Regards

Rena

[amitchandak · Accepted solution]

Add these two columns in table 1

	visitaReales= countx(filter(table2,table1[vendedor]=table2[vendedor]),table2[ClienteCod])
	VisitaMax=if(table1[meta]<=table1[visitaReales],table1[meta],table1[visitaReales])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin