Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tjrosario
Frequent Visitor

How to add calculated column (Ratio) in matrix visuals

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)

 

tjrosario_0-1606387813580.png

 

By excel, I can do it as below.

 

tjrosario_1-1606387910980.png

 

Looking forward for your advise.

Thanks in advance.

 

 

1 ACCEPTED 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:

1.png

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.

2.png

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.

3.png

Final Result is as below.

4.png

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. 

 

View solution in original post

5 REPLIES 5

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

tjrosario_0-1606621061606.png

 

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).

tjrosario_1-1606621135777.pngtjrosario_2-1606621157229.png

 

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:

1.png

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.

2.png

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.

3.png

Final Result is as below.

4.png

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. 

 

@tjrosario 

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.