Add Avg Column to a Matrix

[Oelshamy]

I am kind of new to this or never had a situation to add an extra column to a matrix. 

I am working on a model to visualize a Heat map of the KPI performance for individuals, KPI values are recorded monthly and then have a total column, next to the total column I would like to add values based on a calculated formula, so I created a measure for this.  but I cannot find a way to add it next to the total. 

This is my table - I am using KPI, KPI Date, RAting - calculated the AVG Month as 

Avg_Month = CALCULATE(SUM('KPI Rating'[Rating]) / 50 * CALCULATE(SUM('KPI Master'[Weight (%)]) * 100))

Can someone advise? 

[v-yangliu-msft · Accepted solution]

Thanks for the reply from Ritaf1983 , please allow me to provide another insight: 
Hi  @Oelshamy ,

I created some data:

Power BI sorts alphabetically by default, so we need to create a new table with the columns AVG and rankx.

Here are the steps you can follow：

1. Create calculated table.

Date =
var _table=
CALENDAR(
    DATE(2024,1,1),
    DATE(2024,12,31))
RETURN
ADDCOLUMNS(
    _table,"Month-Year",
    FORMAT([Date],"mmm")&"-"&FORMAT([Date],"yy"))

Table 2 =
var _table=
SUMMARIZE('Date',[Month-Year],
"date",MINX(FILTER(ALL('Date'),'Date'[Month-Year]=EARLIER('Date'[Month-Year])),[Date]))
var _maxdate=MAXX(ALL('Date'),[Date])
var _table2=
{("AVG",_maxdate+1)}
var _table3=
UNION(
    _table,_table2)
return
FILTER(
    _table3,[Month-Year] in SELECTCOLUMNS('Table',"Test",'Table'[Month]) || [Month-Year] ="AVG")

2. Select [Month-Year] -- Column tools – Sort by column – [date].

3. Create measure.

Measure =
IF(
    MAX('Table 2'[Month-Year]) = "AVG",
    AVERAGEX(
        FILTER(ALL('Table'),
        'Table'[KPI]=MAX('Table'[KPI])),[Value]),
    SUMX(
        FILTER(ALL('Table'),
        'Table'[KPI]=MAX('Table'[KPI])&&'Table'[Month]=MAX('Table 2'[Month-Year])),[Value]))

Measure 2 =
IF(
    NOT(ISINSCOPE('Table'[KPI])),SUMX(VALUES('Table'[KPI]),[Measure]),
IF(
    NOT(ISINSCOPE('Table 2'[Month-Year])),SUMX(VALUES('Table 2'[Month-Year]),[Measure]),[Measure])
)

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ritaf1983]

Hi @Oelshamy 
There are a few workarounds to achieve 2 different total columns :
1. With dynamic measures, the video guide :
https://www.youtube.com/watch?v=DIc-KFPr4LI

2. with calculation groups, the guide :
https://www.youtube.com/watch?v=ExCD0vM-OEU

If my answer was helpful please give me a Kudos and accept as a Solution.