Need help with overall percentage for multiple categories

wnicholl — Thu, 20 Feb 2020 18:39:36 GMT

Need to get the overall average percent for bar chart, with the supprting data.

Years as Member	% of Members:	# of Members:
1-10 Years	44.88%	190184
11-20 Years	16.26%	68894
21-30 Years	14.71%	62327
31-40 Years	13.89%	58860
41-50 Years	6.86%	29053
51-60 Years	2.40%	10152
61-70 Years	0.83%	3530
71-80 Years	0.14%	609
81-110 Years	0.03%	123

The total average should be: 11.11%

I have 2 tables that have that have the same caluclated column formulas.

Year Brackets = SWITCH(TRUE(),

'Lead'[Years as Member] <= 10, "1-10 Years",

AND('Lead'[Years as Member] >= 11, 'Lead'[Years as Member] <= 20), "11-20 Years",

AND('Lead'[Years as Member] >= 21, 'Lead'[Years as Member] <= 30), "21-30 Years",

AND('Lead'[Years as Member] >= 31, 'Lead'[Years as Member] <= 40), "31-40 Years",

AND('Lead'[Years as Member] >= 41, 'Lead'[Years as Member] <= 50), "41-50 Years",

AND('Lead'[Years as Member] >= 51, 'Lead'[Years as Member] <= 60), "51-60 Years",

AND('Lead'[Years as Member] >= 61, 'Lead'[Years as Member] <= 70), "61-70 Years",

AND('Lead'[Years as Member] >= 71, 'Lead'[Years as Member] <= 80), "71-80 Years",

AND('Lead'[Years as Member] >= 81, 'Lead'[Years as Member] <= 110), "81-110 Years")

Years as Member = IF( ISBLANK('Lead'[Join_Date__c]), BLANK(), INT( YEARFRAC([Join_Date__c], TODAY() ) ) )

I’ve joined the tables using a lookup table.

Years as Member

1-10 Years

11-20 Years

21-30 Years

31-40 Years

41-50 Years

51-60 Years

61-70 Years

71-80 Years

81-110 Years

Thanks in advance for any suggustions....

Re: Need help with overall percentage for multiple categories

az38 — Sun, 16 Feb 2020 17:05:06 GMT

Hi wnicholl

if i understand you correct the AVERAGEX() function would help you, like

Measure = calculate(AVERAGEX('Table','Table'[% of Members]),all('Table'))

Re: Need help with overall percentage for multiple categories

wnicholl — Mon, 17 Feb 2020 14:45:20 GMT

Thank you for reaching out! I tried the AVERAGEX function and I'm receiving "Can't display the visual".

The combined measure "Name Counts" combines the Lead and Contact fields to give an overall total between the 2 tables. Not sure what I'm doing wrong... Here is my formual I tried.

Years as Member Overall Average =

CALCULATE(AVERAGEX('Combined Measures','Combined Measures'[NAME COUNTS]),ALL('Years Lookup'))

topic Re: Need help with overall percentage for multiple categories in DAX Commands and Tips

Need help with overall percentage for multiple categories

Re: Need help with overall percentage for multiple categories

Re: Need help with overall percentage for multiple categories