Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Matrix Calculated Row

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     ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Community Support
4 years ago

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.