Convert Measure to Calculated Column

[Anonymous]

Hi Experts

Struggling on how i could convert the following measure into a calculated column in my table..

__freq = 
VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_FINANCIAL_PDS[Month Start]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)))
VAR __table = GROUPBY(ComplaintsByFiscalMo,[CountComplaints],"__ties",COUNTX(CURRENTGROUP(),[Month Start]))
VAR __table1 = ADDCOLUMNS(__table,"__ties1",[__ties] - 1)
VAR __table2 = ADDCOLUMNS(__table1,"__freq",IF([__ties1]=0,0,[__ties1]*([__ties1]+1)*(2*[__ties1]+7)))
RETURN
SUMX(__table2,[__freq])

[dax · Accepted solution]

Hi Route217,

My sample is like below

Month Start	value
1	1
1	2
1	3
2	2
2	1
3	2
3	1
3	1
5	1
5	2
5	2
6	1
4	2
4	1

Then you could create calculated column like below

Column = var temp= CALCULATE(COUNT(PMS_COMPLAINT[month start]), ALLEXCEPT(PMS_COMPLAINT,PMS_COMPLAINT[Month Start]))-1 var mul= temp*(temp+1)*(2*temp+7) return mul
Column 2 = SUMX(SUMMARIZE(PMS_COMPLAINT,PMS_COMPLAINT[Column],"avg",AVERAGE(PMS_COMPLAINT[Column])),[avg])

then you will get the result like below

Best Regards,
Zoe Zhi

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