Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am seeking for your advise as I am beginner using PowerBI.
I am currently working on a matrix visual as below and would like to add two more columns in this matrix as
1. Achievement % (Percentage of Achieved/Total)
2. Monthly KPI (e.g for June = Average of Achievement % from January to June)
By excel, I can do it as below.
Looking forward for your advise.
Thanks in advance.
Solved! Go to Solution.
Hi @tjrosario
I build a sample table to have a test.
Due to your data model you could build two measures to achieve your goal.
My Sample Table:
I build a calculated column Rank to rank the Date by year*100+month.
Rank = RANKX('Table',YEAR('Table'[Date])*100+MONTH('Table'[Date]),,ASC,Dense)
Measures:
Achievement% =
VAR _CountAchievement =
COUNTAX (
FILTER ( 'Table', 'Table'[KPI Achievement] = "Achieved" ),
'Table'[KPI Achievement]
)
VAR _Total =
IF (
ISINSCOPE ( 'Table'[Date].[Month] ),
COUNTAX (
FILTER (
ALL ( 'Table' ),
'Table'[Date].[MonthNo] = MAX ( 'Table'[Date].[MonthNo] )
),
'Table'[KPI Achievement]
),
COUNTAX (
FILTER ( ALL ( 'Table' ), 'Table'[Date].[Year] = MAX ( 'Table'[Date].[Year] ) ),
'Table'[KPI Achievement]
)
)
VAR _Result =
DIVIDE ( _CountAchievement, _Total )
RETURN
IF ( _Result = BLANK (), 0, _Result )
Monthly KPI =
IF (
HASONEVALUE ( 'Table'[Date].[Month] ),
IF (
MAX ( 'Table'[Rank] ) <= 5,
BLANK (),
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Rank]
>= MAX ( 'Table'[Rank] ) - 5
&& 'Table'[Rank] <= MAX ( 'Table'[Rank] )
),
[Achievement%]
),
6
)
),
BLANK ()
)
Result is as below.
Due to we add KPI Achievement into Column Field in Matrix visual, so the measure will show result in all rows as above. We need to reduce the width of measures in Achieved,Failed and In Progress columns. We may need to turn off the word wrap in value and column header, or the matrix visual will have a bad display.
Final Result is as below.
You can download the pbix file from this link: How to add calculated column (Ratio) in matrix visuals
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @tjrosario ,
can you show me the table structure, with dummy data, at the backend so that I can change the DAX calculations accordingly.
Hello,
Below are my sample data
Those data I put into Matrix visual as below, however I would like to show as another column the Ratio Achieved/Total as % (not sure if matrix has this function).
Hoping this make sense.
Hi @tjrosario
I build a sample table to have a test.
Due to your data model you could build two measures to achieve your goal.
My Sample Table:
I build a calculated column Rank to rank the Date by year*100+month.
Rank = RANKX('Table',YEAR('Table'[Date])*100+MONTH('Table'[Date]),,ASC,Dense)
Measures:
Achievement% =
VAR _CountAchievement =
COUNTAX (
FILTER ( 'Table', 'Table'[KPI Achievement] = "Achieved" ),
'Table'[KPI Achievement]
)
VAR _Total =
IF (
ISINSCOPE ( 'Table'[Date].[Month] ),
COUNTAX (
FILTER (
ALL ( 'Table' ),
'Table'[Date].[MonthNo] = MAX ( 'Table'[Date].[MonthNo] )
),
'Table'[KPI Achievement]
),
COUNTAX (
FILTER ( ALL ( 'Table' ), 'Table'[Date].[Year] = MAX ( 'Table'[Date].[Year] ) ),
'Table'[KPI Achievement]
)
)
VAR _Result =
DIVIDE ( _CountAchievement, _Total )
RETURN
IF ( _Result = BLANK (), 0, _Result )
Monthly KPI =
IF (
HASONEVALUE ( 'Table'[Date].[Month] ),
IF (
MAX ( 'Table'[Rank] ) <= 5,
BLANK (),
DIVIDE (
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Rank]
>= MAX ( 'Table'[Rank] ) - 5
&& 'Table'[Rank] <= MAX ( 'Table'[Rank] )
),
[Achievement%]
),
6
)
),
BLANK ()
)
Result is as below.
Due to we add KPI Achievement into Column Field in Matrix visual, so the measure will show result in all rows as above. We need to reduce the width of measures in Achieved,Failed and In Progress columns. We may need to turn off the word wrap in value and column header, or the matrix visual will have a bad display.
Final Result is as below.
You can download the pbix file from this link: How to add calculated column (Ratio) in matrix visuals
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can create a calculated column for both the calculations using DAX
1.
Achievement% = Table_Name[Achieved]/Table_Name[Failed]+Table_Name[InProgress]
Change format of this column to % from the Format option available in the ribbon above and use respective table and column names
2.
Monthly_KPI =
var Month = Month(Table_Name[Date])
var Year = Year(Table_Name[Date])
return (sum(Table_Name[Achieved]/Table_Name[Failed]+Table_Name[InProgress])/Month)
Use respective table and column names.
If this helps you, please mark this as a solution
Veena Shenolikar
Thanks Veena. However my the Achieved, In Progress, Failed are values under the column KPI Achievement so the DAX you've given seems will not work. Is there a way that I can copy the matrix visual values and paste into another table?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |