cancel
Showing results for
Did you mean:
Regular Visitor

## DAX

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.

Amz

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: DAX

Hi @Amz_123,

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

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

## Re: DAX

Hi @Amz_123,

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

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

## 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.