Forum Discussion
Add Avg Column to a Matrix
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
Can someone advise?
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
2 Replies
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-KFPr4LI2. with calculation groups, the guide :
https://www.youtube.com/watch?v=ExCD0vM-OEUIf my answer was helpful please give me a Kudos and accept as a 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
