topic Re: Percentage of grand total per month in DAX Commands and Tips

Percentage of grand total per month

mhbon — Thu, 04 Feb 2021 02:45:57 GMT

Hi everybody

I need to calculate the percentage of a grand total in a measure. I would use this to compare the quantities of food I want to track against all the food I have in my data (even the ones I am not tracking in this visual)

Food	Jan 2021	Feb 2021
Pizza	10%	25%
Spaghetti	25%	50%

The data looks like this:

Food	Date	Quantity
Broccoli	2 jan 2021	4
Pizza	3 jan 2021	1
Bread	4 jan 2021	5
Spaghetti	5 jan 2021	2
Carrots	6 jan 2021	4
Spaghetti	7 jan 2021	3
Pizza	8 jan 2021	1
Carrots	8 feb 2021	1
Pizza	15 feb 2021	1
Spaghetti	21 feb 2021	2
...	....	...

I already tried diffrent ways of using the ALL() filter and its many varieties but I'm really not getting anywhere.

Really greatfull if someone could help out!

Thanks in advance!

M

Re: Percentage of grand total per month

MattAllington — Thu, 04 Feb 2021 02:56:30 GMT

Read my article here https://exceleratorbi.com.au/the-all-function-in-dax/

towards the bottom, using ALL in a measure.

Re: Percentage of grand total per month

mhbon — Thu, 04 Feb 2021 03:15:02 GMT

Hi Matt,

Thanks for your fast response. I stumbled across your article while searching for a way to resolve this issue before. However I find that when excluding the records containing Broccoli and Carrots from my visual, these do not get included in the grand total.

I realise that this might just be impossible since I choose to exclude Borccoli and Carrots from my visual.

Re: Percentage of grand total per month

MattAllington — Thu, 04 Feb 2021 03:25:09 GMT

I couldn't say for sure if ALL can remove filters excluded in a visual. It may depend how you exclude them. It should be simple to test with a test measure using all. Just test various exclusion options including slicers and the filter pane