Matrix Calculated Row

Anonymous — Thu, 07 Jul 2022 04:25:15 GMT

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.

Re: Matrix Calculated Row

v-yalanwu-msft — Mon, 11 Jul 2022 07:27:14 GMT

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.

topic Matrix Calculated Row in DAX Commands and Tips

Matrix Calculated Row

Re: Matrix Calculated Row