Forum Discussion
Matrix Calculated Row
Hi All, Please help me in calculted row in a Matrix PowerBI:
|
I need to create a new Row 2021-Avg Jobs = -10/9 200/9 300/9
2022-Avg Jobs = -30/12 400/12 500/12
I have Hierarchy of Year,QTR,MONTH and Member.
Please advise me with DAX.
Hi， Anonymous ，
You could create a table .
Table 2 = UNION(VALUES('Table'[Year]), DISTINCT( SELECTCOLUMNS('Table',"1",[Year]&"aver")))Then use it as rows:
and create a measure.
2021-Avg Jobs = var _sum=CALCULATE(SUM([number]),FILTER('Table',FORMAT( [Year],"0")=MAX('Table 2'[Year]))) var _aver=CALCULATE(SUM([number]),FILTER('Table',FORMAT( [Year],"0")=LEFT( MAX('Table 2'[Year]),4))) var _aver2=CALCULATE(SUM([number]),FILTER(ALLSELECTED('Table'),FORMAT( [Year],"0")=LEFT( MAX('Table 2'[Year]),4)&&[Column1]="Jobs")) return IF(CONTAINSSTRING( MAX('Table 2'[Year]),"aver"),DIVIDE(_aver,_aver2),_sum)The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi， Anonymous ，
You could create a table .
Table 2 = UNION(VALUES('Table'[Year]), DISTINCT( SELECTCOLUMNS('Table',"1",[Year]&"aver")))Then use it as rows:
and create a measure.
2021-Avg Jobs = var _sum=CALCULATE(SUM([number]),FILTER('Table',FORMAT( [Year],"0")=MAX('Table 2'[Year]))) var _aver=CALCULATE(SUM([number]),FILTER('Table',FORMAT( [Year],"0")=LEFT( MAX('Table 2'[Year]),4))) var _aver2=CALCULATE(SUM([number]),FILTER(ALLSELECTED('Table'),FORMAT( [Year],"0")=LEFT( MAX('Table 2'[Year]),4)&&[Column1]="Jobs")) return IF(CONTAINSSTRING( MAX('Table 2'[Year]),"aver"),DIVIDE(_aver,_aver2),_sum)The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.