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

Reply
Raul09
Helper I
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

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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

 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

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.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

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!

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

 

 

 

 

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.

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors