Percentage of total by Product

[Raul09]

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.

[Greg_Deckler]

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.

[Raul09]

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

[Raul09]

 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

[Greg_Deckler · 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.

[anakoom]

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

[Greg_Deckler]

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

[LouisBI]

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.

[LouisBI]

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!

[anakoom]

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

[Raul09]

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.

[v-caliao-msft]

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