cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Percentage of total by Product

Hi,

i'm working on a sales report that includes all 2017 sales by month.

I have a sheet that displays Sales by Product family using a date filter, now i need to add to every product family a % of total sales in that month.

example:

Total March Sales 100.000\$

Product family A - 20.000\$   - 20%
Product family B - 80.000\$    - 80%

The problem is when i calculate the % is showing percentage of ALL the sales recorded on my data (2017 total sales), and is not using my date filter.

how can i get this done?

1 ACCEPTED SOLUTION
Super User

I think you should change your formula to this:

`MyMeasure = SUM('Sheet1'[Amount]) / CALCULATE(SUM('Sheet1'[Amount]),FILTER('Sheet1',ALLEXCEPT([month])))`

What this should do is calculate the percentage that you want. Put this measure into a visualization that utilizes family and month along with this measure. Perhaps a matrix or something along those lines. What should happen is that the context of the visualization will ensure that the correct sums are calculated. The ALLEXCEPT will remove the family context but ensure that the month context is kept, which sounds like what you want.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
11 REPLIES 11
Super User

First, I'm assuming that you are using a measure for this calculation. If not, use a measure for this calculation. Second, if you are not using ALLEXCEPT, you should be with the exception being your month or date column.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

I'm really new at Power BI, can you elaborate on your solution please?

Let's say my sales \$ column name is AMOUNT, and date column name is DATE

Helper I

Ok, i tried using a measure and it works

Family A % = CALCULATE(SUM('Sheet1'[Amount]);'Sheet1'[Family] = "FamilyA")/SUM('Sheet1'[Amount])

now, do i have to create a measure for every family?

i've tried using visual level filters with other formula but it isn't working

Super User

I think you should change your formula to this:

`MyMeasure = SUM('Sheet1'[Amount]) / CALCULATE(SUM('Sheet1'[Amount]),FILTER('Sheet1',ALLEXCEPT([month])))`

What this should do is calculate the percentage that you want. Put this measure into a visualization that utilizes family and month along with this measure. Perhaps a matrix or something along those lines. What should happen is that the context of the visualization will ensure that the correct sums are calculated. The ALLEXCEPT will remove the family context but ensure that the month context is kept, which sounds like what you want.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Helper I

Hi I have similar issue if you can help with this

would you be help me with my case: as it is not calculating the correct one:

startdate , subdate, amount,%oftotal

1/1/2016   2/1/2016  100      50%

2/2/2016  100      50%

-----------------------------------------

200     100%

i wants to see similar like above

appriciate if you can help me

thanks

Super User

You should be able to use a Quick Measure, Percent of Total in a matrix?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

You sir, helped me out a lot! I was strugglin with a formula but your comment fixed it within a few seconds.
I hope you'll have a very nice year. Thank you once again.

Regular Visitor

I've been strugglin to fix the formula but it didn't work.
You sir, you helped me out a lot! Thank you very much and I hope you have a great year!

Helper I

Thanks it works using quick calculations..

i want to calcuate the sum of this value by Adate which is>= getdate()

i have tried using summarize function but did not get the proper data

thanks

Helper I

That formula isn't working for me.

Too few arguments were passed to the ALLEXCEPT function. The minimum argument count for the function is 2.

Microsoft

Hi @Raul09,

The minimum argument count for the ALLEXCEPT function is 2. The first argument to the ALLEXCEPT function must be a reference to a base table; all subsequent arguments must be references to base columns.

https://msdn.microsoft.com/en-us/library/ee634795.aspx?f=255&MSPPError=-2147217396

Regards,

Charlie Liao

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors