Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])
Solved! Go to 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.
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.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |