Solved: Display percentage instead of total in matrix visu...

trg7ija

Hi, I am new to Power BI.

I use matrix visual but the subtotal display the total of the column.

Like this visual

But I expect to be like this

So the percent will calculate to total of each column dan calculate the percetage based on total number of Names.

Thank you very much in advance

DataNinja777

Hi @trg7ija ,

To achieve the desired percentage display in your Power BI matrix, you first need to reshape your data and then create a specific calculation. The core idea is to transform your data from a wide format, where each question is its own column, into a tall format that is more suitable for analysis. You can accomplish this within the Power Query Editor by selecting the "NAME" column, navigating to the "Transform" tab, and choosing to "Unpivot Other Columns." This action will create two new columns, which you should rename for clarity to "Question" (formerly "Attribute") and "Value". After applying these changes, you will return to the main Power BI window.

With the data correctly structured, the next step is to create a DAX (Data Analysis Expressions) measure to calculate the percentage. Right-click on your table in the Data pane, select "New measure," and then enter the following formula. This code calculates the percentage by dividing the sum of correct answers (the '1's) for each question by the total number of respondents in your dataset.

Percentage Score =
VAR SumOfOnes =
    SUM ( 'YourTableName'[Value] )
VAR TotalNames =
    CALCULATE (
        DISTINCTCOUNT ( 'YourTableName'[NAME] ),
        ALL ( 'YourTableName'[NAME] )
    )
RETURN
    DIVIDE ( SumOfOnes, TotalNames )

Remember to replace 'YourTableName' with the actual name of your data table. Once the measure is created, select it in the Data pane, go to the "Measure tools" tab in the ribbon, and click the '%' icon to format it as a percentage. Finally, to build your visual, drag the "NAME" field to the Rows area, the "Question" field to the Columns area, and your newly created [Percentage Score] measure into the Values area of the matrix visual. This will replace the simple count totals with the calculated percentages for each column total row as you intended.

Best regards,

View solution in original post

DataNinja777

Hi @trg7ija ,

To achieve the desired percentage display in your Power BI matrix, you first need to reshape your data and then create a specific calculation. The core idea is to transform your data from a wide format, where each question is its own column, into a tall format that is more suitable for analysis. You can accomplish this within the Power Query Editor by selecting the "NAME" column, navigating to the "Transform" tab, and choosing to "Unpivot Other Columns." This action will create two new columns, which you should rename for clarity to "Question" (formerly "Attribute") and "Value". After applying these changes, you will return to the main Power BI window.

With the data correctly structured, the next step is to create a DAX (Data Analysis Expressions) measure to calculate the percentage. Right-click on your table in the Data pane, select "New measure," and then enter the following formula. This code calculates the percentage by dividing the sum of correct answers (the '1's) for each question by the total number of respondents in your dataset.

Percentage Score =
VAR SumOfOnes =
    SUM ( 'YourTableName'[Value] )
VAR TotalNames =
    CALCULATE (
        DISTINCTCOUNT ( 'YourTableName'[NAME] ),
        ALL ( 'YourTableName'[NAME] )
    )
RETURN
    DIVIDE ( SumOfOnes, TotalNames )

Remember to replace 'YourTableName' with the actual name of your data table. Once the measure is created, select it in the Data pane, go to the "Measure tools" tab in the ribbon, and click the '%' icon to format it as a percentage. Finally, to build your visual, drag the "NAME" field to the Rows area, the "Question" field to the Columns area, and your newly created [Percentage Score] measure into the Values area of the matrix visual. This will replace the simple count totals with the calculated percentages for each column total row as you intended.

Best regards,