dax

[Anonymous]

BE	STL	TGT	Sale
Abhay Chourasiya	Abhay Chourasiya	9.853063	77
	Dharmendra  Kherwal	81.48438	11
	Dinesh  Patil	74.49055	53
	Rakesh  Admane	76.77411	34
	Ravindra  Deshmukh	70.66465	235
	Sandeep  Chouhan	106.01	192
Amol Dalvi	Amol Dalvi	16.15335	114
	Muntazir Shakil Ahemad	90.03278	259
	Rupesh  Raut	122.7732	161
	Sandeep  Chaudhary	87.55578	267
	Siddharth  Khobragade	126.3647	175
Anirudh Singh Chouhan	Deepak  Sahu	58.61291	263
	Santosh  Soni	226.0715	229
	Satish  Chaturvedi	74.09938	30
	Shrikant  Dubey	119.2013	251
	Sourabh  Jaiswal	99.55368	143
HFS BE	Nilesh  Bhoyar	138.1463	97
	Rahul  Ambulkar	96.67501	75
Mahendra Vyas	New STL Nagda	42.57484	34
	Rajesh  Pathak	109.9209	257
	Satyam  Dhamande	44.74544	254
	Vishnu Tagad	100.3021	154
Manish	Manish	14.76656	81
Manish Upadhyay	Ankur  Vaidya	140.8746	97
	DUMMY	0	81
	Manish Upadhyay	0	94
Manoj Panchgam	ANWAR  SHEIKH	154.5587	117
	GAURAV  MAHAJAN	133.2221	231
	Girish  Khirpurkar	110.9362	235
	Manoj Panchgam	6.70613	78
	Vishal  Mahajan	142.642	30
MR Nagpur	Gaurav  Ghotekar	185.7865	136
	Mitesh  Adatiya	91.3	218
Rupali Datta	Nagpur Ecom STL	277.002	225
Rupali(HFS)	Rupali datta	34.71601	90
Rupali_Datta	Rupali datta	200	226
Saurabh Shenad	Atul Gatlewar	105.7472	138
	Pawan  Kakkad	85.41462	81
	Saurabh  Shenad	43.6858	215
	Vishnu Patil	33.8123	275
	Vrushabh Jain	87.96069	28
Sunil Giramkar	Deepika jaiswal	500	263
	Sunil Giramkar	0	132
Ujala Deshmukh	Bhumesh  Nagpure	69.17327	114
	Ganesh  Chutelkar	215.2937	49
	Kishor  Shinde	102.0369	11
	Nishant  Lanjewar	60.2269	218
	Shakir  Mohammad	73.04434	14
	Sumit  Bajaj	59.55782	116
	Ujala  Deshmukh	56.38541	219
Viru Moon	Alok Jadhav	69.62336	248
	Mukesh  Pihulkar	57.49001	213
	Rajesh  Mohare	91.27349	19
	Sunil  Moon	164.1718	270
TOTAL		5339.468	7927

1)here i have sales vs target and i want to divide sales and target and then convert to percentage.
2)If that percentage is >= 100 then it should return 1 else 0.
3)Also i want to count how many times 1 is being returned.

[Ritaf1983 · Accepted solution]

Hi @Anonymous 
If i understood you correctly the measures to use are :
1.

% Actual = sumx('Table',DIVIDE([Sale],[TGT]))

2. 

flag = if([% Actual]>1,1,0)

3.

counter = SUMX(

SUMMARIZE(VALUES('Table'[STL]),"Counter_1", CALCULATE(COUNTROWS('Table'),FILTER('Table',[flag]=1))),[Counter_1])

Result :

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi @Anonymous 
If i understood you correctly the measures to use are :
1.

% Actual = sumx('Table',DIVIDE([Sale],[TGT]))

2. 

flag = if([% Actual]>1,1,0)

3.

counter = SUMX(

SUMMARIZE(VALUES('Table'[STL]),"Counter_1", CALCULATE(COUNTROWS('Table'),FILTER('Table',[flag]=1))),[Counter_1])

Result :

pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Anonymous]

Hii @Ritaf1983 
I have 60k STL's. And this formula is not working for huge data. 

[Ritaf1983 · Accepted solution]

@Anonymous 
60K is not so huge..if you are using an import mode.
If the report isn't working with the formula with the virtual table.
Try to use a calculated column.

Actual % = divide('Table'[Sale],[TGT])

and for counting the measure :

count_2 = CALCULATE(COUNTROWS('Table'),'Table'[Actual %]>1)

the updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly