Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Solved! Go to 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
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
Hi @Amz_123,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |