Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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.
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.
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.
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?
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |