cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Amz_123 Regular Visitor
Regular Visitor

DAX

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

Here is my sample data,
Sample.png

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

2017 - 1.pngLeft is Matrix Visual has Count and Right is Percentage.2018-2.png2016-3.png
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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX

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.

 

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX

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.

 

Capture.PNG

For more details, please check the pbix as attached.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: DAX

Hi @Amz_123,

 

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


Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.