Forum Discussion

Amz_123's avatar
Amz_123
Helper I
7 years ago
Solved

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.

Thanks in advance,
Amz

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    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's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Amz_123,

       

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


      Regards,
      Frank