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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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?

Thanks in advance.

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

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

Thanks for the Reply.

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

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?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

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

thanks for the reply.

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 Employee

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors