Create a matrix visual showing percentages

[Sadieh1]

Hi,

I have a matrix visual which shows amounts of parts by month, but I want to convert these amounts to % (% of the total for each month). So for June 24 Screen I want to show as 35%, June Misc 13%, July Cover 17% so and so forth

Can anyone help on how to build a measure that helps me calculate this.

Thank you

Sadie

[Sadieh1]

you @bchager that has worked 🙂

[Sadieh1]

@saud968 I don't quite follow the part where you say 'replace your table amount with actual name of column'... These are my current fields selected for the matrix visual - see below. So 'my table amount' is sum of sold count. I needs the rows and columns to remain the same I just want the figures replacing with % (on a new matrix visual)...but I can't quite follow your suggestion

[Sadieh1]

Really new to BI...I need a seprate matrix same format as above but shows me the % isnrtead of the figures for each part in each month. @danextian neither of the measures work if I drop that measure into the values field of the matrix. @saud968 let me look and see if your suggestion works - will feed back

Many thanks

[bchager · Accepted solution]

@Sadieh1 This will work once you unpivot the date columns. A single measure can be used with this approach instead of creating one for each date. A sample .pbix is attached.

Percentage of Column Total =

DIVIDE(

    SUM('Table'[Value]),

    CALCULATE(SUM('Table'[Value]), REMOVEFILTERS('Table'[Part]))

))

[danextian]

Hi @Sadieh1 

Try these measures:

DIVIDE (
    SUM ( 'table'[column to sum] ),
    CALCULATE ( SUM ( 'table'[column to sum] ), ALL ( 'table'[part] ) ) --calculate the sum of all parts and apply that to all parts row
)


DIVIDE (
    SUM ( 'table'[column to sum] ),
    CALCULATE ( SUM ( 'table'[column to sum] ), ALLSELECTED ( 'table'[part] ) ) --calculate the sum of all visible/ selected parts and apply that to all parts row
)

[saud968]

Here's how you can create a measure to calculate percentages within a Matrix visual in Power BI:

1. Create a Measure for Total:

First, create a measure to calculate the total for each month:

Total = SUM(YourTable[Amount])
Replace YourTable[Amount] with the actual name of your column containing the parts amounts.

2. Create a Measure for Percentage:

Then, create a measure to calculate the percentage of each part within the total for that month:

Percentage = DIVIDE(SUM(YourTable[Amount]), [Total])

3. Format the Percentage Measure:

Right-click on the Percentage measure in the Fields pane and select "Format."
In the "Number" category, choose the "Percentage" format and set the decimal places as needed.

4. Add the Measures to the Matrix Visual:

Drag the Percentage measure into the Values section of the Matrix visual.
Configure the rows and columns of the Matrix as needed.
5. Configure the Matrix Visual:

Right-click on the Percentage column in the Matrix visual and select "Sort by Column."
Choose the Percentage measure to sort the values in descending order.
Example:

Assuming your table is named "PartsData" with columns "Part," "Month," and "Amount," here's how the measures would look:

Total = SUM(PartsData[Amount])

Percentage = DIVIDE(SUM(PartsData[Amount]), [Total])

Additional Considerations:

If you want to calculate percentages based on a different total (e.g., the total for a specific part across all months), you can modify the [Total] measure accordingly.
You can use the FORMAT function to customize the display of percentages, such as adding a percentage sign or formatting decimals.
If you have a large number of parts, you might want to consider using a slicer or filter to narrow down the data displayed in the Matrix visual.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!