Matrix Calculated Row

[Anonymous]

Hi All, Please help me in calculted row in a Matrix PowerBI: 

year Quote Number In Process Number PCA Number Total Jobs 2021 -10 200 300 9 2022 -30 400 500 12

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.

[v-yalanwu-msft · Accepted solution]

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.