DAX

[Amz_123]

Hi Community,
Help me with DAX for finding the average of percentage Based on Slicer Selection.

Here is my sample data,


For Single Year Selection, Percentage of Each Category for each month is 

Left is Matrix Visual has Count and Right is Percentage.
I am Struck with multiple Slicer Selection,When 2017 and 2018 are selected,i needed to visualize as above for all category in matix as,
Average of Percentage of selected two years - For Example, Category "b", for Feb, I need Dax to get value ((50%(i.e % obtained from 2017)+50% (i.e% obtained from 2017))/2(i.e for two Years)

Similiarly  when three years are selected, Average must be calculated for three years. Please assist me in finding the DAX Solution.

Thanks in advance,
Amz

[v-frfei-msft · Accepted solution]

Hi @Amz_123,

I made one sample for your reference.

1. Create a calculated table.

Table = VALUES(Table1[Year])

2. Create the measures as below.

maxre = var maxy =CALCULATE( MAX('Table'[Year]),ALLSELECTED('Table'))
var miny = CALCULATE(MIN('Table'[Year]),ALLSELECTED('Table'))
var minre =CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=miny)) 
var maxre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=maxy))
var midre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]<>maxy&& Table1[Year]<> miny))
return CALCULATE(COUNT(Table1[Category]),FILTER('Table1',Table1[Year]=maxy))

midre = var maxy =CALCULATE( MAX('Table'[Year]),ALLSELECTED('Table'))
var miny = CALCULATE(MIN('Table'[Year]),ALLSELECTED('Table'))
var minre =CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=miny)) 
var maxre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=maxy))
var midre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]<>maxy&& Table1[Year]<> miny))
return CALCULATE(COUNT(Table1[Category]),FILTER('Table1',Table1[Year]=midre))

minre = var maxy =CALCULATE( MAX('Table'[Year]),ALLSELECTED('Table'))
var miny = CALCULATE(MIN('Table'[Year]),ALLSELECTED('Table'))
var minre =CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=miny)) 
var maxre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]=maxy))
var midre = CALCULATE(COUNT(Table1[Category]),FILTER(Table1,Table1[Year]<>maxy&& Table1[Year]<> miny))
return CALCULATE(COUNT(Table1[Category]),FILTER('Table1',Table1[Year]=miny))

single = CALCULATE(COUNT(Table1[Category]),FILTER('Table1',Table1[Year]=SELECTEDVALUE('Table'[Year]))
)

smax = SUMX(ALLSELECTED(Table1[Category]),[maxre])

smid = SUMX(ALLSELECTED(Table1[Category]),[midre])

smin = SUMX(ALLSELECTED(Table1[Category]),[minre])

result = var county =CALCULATE(DISTINCTCOUNT('Table'[Year]),ALLSELECTED('Table'[Year])) 
return
IF(county=1,[single]/[smin],IF(county>1 && county<3,([maxre]/[smax]+[minre]/[smin])/2,IF(county=3,([maxre]/[smax]+[midre]/[smid]+[minre]/[smin])/3)))

Then we can get the result as we need.

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @Amz_123,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank