Group by & Count of employee

[refint650]

Hi ALL.

i'm doing measure calculation with custom group of columns for sales amount and purchase amount from single table

1st variable does calculate sum of salesamount for 5 of columns.

2nd variable does calculate sum of purchase amont for same 5  columns.

 3rd variable does %margin calculation (sales/profit). 

now my requirement i want distinct count of employees with margin % between >5% and <90%  from the above groupby column combination only because of data structures. so can i same filter condition in same variance measure.?

Variance = 

VAR _sales = GROUPBY(TableX, State, City, Product, EmployeeID, Date,"Sales", SUMX(CURRENTGROUP(), [SalesAmount))

VAR _Profit = GROUPBY(TableX, State, City, Product, EmployeeID, Date,"Purchase", SUMX(CURRENTGROUP(), [PurchaseAmount))

Var _Margin  =Divide( CALCULATE(SUMX(_Sales,[SalesAmount]),ALLEXCEPT(TableX, [DATE])) , CALCULATE(SUMX(_Profit,[PurchaseAmount]),ALLEXCEPT(TableX,[DATE])))
return _Margin.

I tried doing new calcuation  on  top of [Variance] Measure didnt work well either some ws are missing  data is not accurate or outer filters not applying within measure scondition,Much appreciated for help 🙂

file attachment

https://www.dropbox.com/scl/fi/fkj67r7t8iyzz8q58xfs0/Thresholddata.xls?dl=0&rlkey=aa6zbwd3xhwccvkucf...

IF ( [Variance] < 90 ,CALCULATE ( DISTINCTCOUNT ( TableX[EmployeeID]), FILTER ( ALL ( 'TableX' ), [Variance] < 1 )),0 )

[Ashish_Mathur · Accepted solution]

Hi,

Going by your description, this measure should work

=countrows(filter(values(TableX[EmployeeID]),[Margin]>0.05&&[Margin]<0.90))

Hope this helps.

[refint650]

@Ashish_Mathur 

expression worked for 85% of data. but i figured we have to apply filter expression on both variable sales & purchase after group by condition to drop rows with 0 amount values. then your provided filter should work 100%

[Ashish_Mathur]

You marked my response as Answer.  So has my formulas resolved your problem?