Do calculation on cell level

[kalpanaV]

Hi

Here, i want to display in the new column  for the first row (2/45)1*12

second row =( (2+3)/24)2*12

third row =((3+2+1)/30)/3*12

how to write dax.

[tringuyenminh92]

Hi @kalpanaV,

Your fomular is not clear. but you could create calculated column:

% attr = 
var currentIndex= Data[Index]
var currentHC= Data[HC]
var total = CALCULATE(SUM(Data[V]),FILTER(Data,Data[Index]<=currentIndex))
return 12* DIVIDE(total,Data[HC]*currentIndex)

[kalpanaV]

hi 

Thanks 

but the thing is,  i have to take HC from other table which is having four months of data.

V column is the one which i have to take count having only V from another column in the same table.

hope, i explained clear now

[Anonymous]

It would help if you would include your model and desired results.

[kalpanaV]

Sure, I will give the headers since the data is confidential.

AttritionCapacity

Two tables called Attrition and Capacity

In the attrition table , column "Resignation type"..its having info about Voluntary(V) or Involuntary(NV).

In the capacity table, four months of data is there. i grouped by month wise

so finally it will be having two columns called count(HC), Month.

Output

Hope , you got it. what i want. 

[tringuyenminh92]

Hi @kalpanaV,

In this case, you could use @Anonymous (using calculated measure) or my solution (using calculated column), both of them will achieve the column V % (YTD attrition %) for you. For overall YTD, hope you provide its fomular and we could suggest more expression.

[kalpanaV]

i tried , i didnt get the right answer.

[v-chuncz-msft]

@kalpanaV,

To get a much more effective solution, please simplify your model with dummy data and show us the exact output.

[kalpanaV]

AttritionCapacityOutput

HC is the head count of Capacity table

V and NV is resignation type.rest of all we have to calculate.

formula used :        % attr =
var currentIndex= Attrition[Month_Key]
var currentHC= Capacity[Month_Wise]
var total = CALCULATE(SUM(Attrition[User Id]),FILTER(Attrition,Attrition[Month_Key]<=currentIndex))
return 12* DIVIDE(total,Capacity[Month_Wise]*currentIndex)

[kalpanaV]

can anyone , help me on this?

[tringuyenminh92]

Hi @kalpanaV,

I got your situation cause i'm used to work with HR data, for beginner, the simple way is you use "group by" (Summarize method or Addcolumns with Summarize method) to generate new table like your picture. Or you could let me observe your sample transaction data to have proper solution(cause i'm not sure that could achieve your requirement with one measure expression)

[Anonymous]

Hi @kalpanaV,

You should use a iterator function like SUMX to do row based calculations. 

The formula might need a little change because only in the third row i see you divide the result by 3*12 and because there is no desired result please check if the formule is fully correct.

First define a measure that counts the V column as a running total, I called it YTD but actually it uses the Index now. Consider using a date column so you could use an actual YTD function. Below the V_YTD and calculation like described.

V_YTD = 
VAR CurrentIndex = MAX('Table'[Index])
RETURN
CALCULATE( SUM('Table'[v]);
	FILTER(
		ALLSELECTED('Table');
		'Table'[Index] <= CurrentIndex
	))

NewCalc = 
VAR V = SUM('Table'[v])
VAR HC = SUM('Table'[HC])
VAR V_YTD = [V_YTD]
RETURN
SUMX(
	'Table';
	(V_YTD/HC)/'Table'[Index] * 12)

Result: