Specifying percentages of a total (no distribution)

[Macfly123]

Hello everyone,

I would like to specify the percentage values in a table based on a sum from a measure. But not a percentage distribution, rather, for example, there were 50 sales of product Teddy1 and 10 sales of Teddy2. There were 100 sales in total, so the output for Teddy1 should be 50% and for Teddy2 10%. How can I do this?

Thanks and best regards,
Chris

[v-kpoloju-msft]

Hi @Macfly123,

Thank you for raching out to the Microsoft Fabric Community Forum. Also, thanks to @Ashish_Mathur, @srlabhe, @burakkaragoz, @MFelix, for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member @Ashish_Mathur, @srlabhe, @burakkaragoz, @MFelix, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[Ashish_Mathur]

Hi,

You may also explore visual calculations.  To receive specific help, share the download link of the PBI file and show the expected result there.

[srlabhe]

To display specific items' sales as a percentage of the grand total in a Power BI table, you need to create a new DAX measure that calculates the grand total of all sales by ignoring any product-specific filters. 

DAX Measure for Percentage of Total

1. Create a base measure for your sales if you don't have one already. This simply sums up the sales values.
   dax

   Total Sales = SUM('YourTableName'[Sales])

2. Create the percentage measure using DIVIDE and CALCULATE with REMOVEFILTERS (or ALL) to get the grand total.
   dax

   Percentage of Total Sales = 
   DIVIDE(
       [Total Sales],
       CALCULATE(
           [Total Sales],
           REMOVEFILTERS('YourTableName'[Product]) -- Replace 'Product' with your actual product column name
       )
   )

   • [Total Sales] in the numerator calculates the sales for the current product in the table visual (e.g., Teddy1 sales).
   • CALCULATE([Total Sales], REMOVEFILTERS(...)) calculates the sales while ignoring any filters on the [Product] column, effectively giving you the grand total across all products.
   • DIVIDE safely handles cases where the denominator might be zero. 

Steps to Implement in Power BI

1. Open your Power BI report.
2. In the Fields pane, right-click on your data table and select New measure.
3. Paste the Percentage of Total Sales DAX formula into the formula bar. Be sure to replace 'YourTableName' and 'Product' with your actual table and column names.
4. Commit the measure and then select the new measure in the Fields pane.
5. In the Measure tools ribbon at the top, format the measure as a Percentage using the % button. You can also adjust the number of decimal places here.
6. Add a Table or Matrix visual to your report.
7. Add your Product column to the rows/categories and the new Percentage of Total Sales measure to the Values well. 

This will give you a table where each product's percentage is calculated against the overall grand total (e.g., Teddy1: 50%, Teddy2: 10%, assuming 100 total sales). 

If you want the percentages to respect other filters like a date slicer (meaning the total adapts to the selected date range but still ignores the product filter), replace REMOVEFILTERS('YourTableName'[Product]) with ALLSELECTED('YourTableName'[Product]). 

[burakkaragoz]

Hi @Macfly123 ,

@MFelix  is spot on with the formula pattern.

To give you a bit more context on why this works, Power BI calculates every row independently. Without the CALCULATE and REMOVEFILTERS (or ALL) part, Power BI would only see "Teddy1" for both the numerator and the denominator, giving you 100% for every row.

By using REMOVEFILTERS, you are telling the measure to "ignore what product is on this row and give me the grand total of everything."

One Pro Tip on "Visual Totals": If you want your percentage to recalculate based on slicers (for example, if you filter to show only "Blue Toys" and you want the total to be 100% of just the blue ones), you should use ALLSELECTED instead.

Here is the difference:

• REMOVEFILTERS / ALL: Calculates % against the entire database (Global Total).

• ALLSELECTED: Calculates % against whatever is currently visible in your report (Visual Total).

And lastly, don't forget to select your new measure and click the % icon in the Measure Tools tab at the top to format it correctly!

Hope this adds some clarity to the solution!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[MFelix · Accepted solution]

Hi @Macfly123 ,

For this you need to do a measure similar to this one:

Percentage of total = DIVIDE ( SUM(Table[Column]), CALCULATE(SUM(Table[Column]), REMOVEFILTERS(Table[Product]))

Be aware that depending on model this may need to be with a different syntax.