Forum Discussion
Calculation in table changes based on value selected in slicer
Hello,
below is an example of a maxtix table i created with measures. I have a slicer I am using for Gender the values are either Male or Female. When i select the value for female, i want the calcuations below to update, for example, for the column, Total Assessed Employees, i want the calculation to be the number of females assessed (10) divided by the total number of assessed employees (50), so it would show 20% when female is selected in the slicer. Currently, when i use the slicer it is taking the number of females assessed divided by the total number of females. I want the number of females assed divided by the total assessed females. is this possible?
| Compay | Total Employees | Total Assessed Employees | Needs Improvement Associates |
| A | 100 | 50% (50\100) | 50% (25/50) |
| B | 200 | 75% (150/200) | 50% (75/150) |
Hi, mmills2018
Can this output finally meet all your requirement? I think I have tried my best😅:
This is the formula of the two measures:
Total Assessed Employees = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Total Assessed"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Total Assessed"),[ID]) var _all= COUNTX(ALL('Table'),[ID]) var _percent= DIVIDE(_value,_total) var _totalpercent= DIVIDE(_total,_all) var _result= IF(_value=BLANK(),0,_percent) var _result1= FORMAT(_result,"Percent")&"("&IF(_value=BLANK(),0,_value)&"/"&_total&")" return IF(HASONEVALUE('Table'[Company]),_result1, IF(ISFILTERED(Gender[Gender]),_result1, FORMAT(_totalpercent,"Percent")&"("&_total&"/"&_all&")"))Needs Improvement Associates = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Needs Improvement"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Needs Improvement"),[ID]) var _all= COUNTX(ALL('Table'),[ID]) var _percent= DIVIDE(_value,_total) var _totalpercent= DIVIDE(_total,_all) var _result= IF(_value=BLANK(),0,_percent) var _result1= FORMAT(_result,"Percent")&"("&IF(_value=BLANK(),0,_value)&"/"&_total&")" return IF(HASONEVALUE('Table'[Company]),_result1, IF(ISFILTERED(Gender[Gender]),_result1, FORMAT(_totalpercent,"Percent")&"("&_total&"/"&_all&")"))You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
16 Replies
Hi, mmills2018
Can this output finally meet all your requirement? I think I have tried my best😅:
This is the formula of the two measures:
Total Assessed Employees = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Total Assessed"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Total Assessed"),[ID]) var _all= COUNTX(ALL('Table'),[ID]) var _percent= DIVIDE(_value,_total) var _totalpercent= DIVIDE(_total,_all) var _result= IF(_value=BLANK(),0,_percent) var _result1= FORMAT(_result,"Percent")&"("&IF(_value=BLANK(),0,_value)&"/"&_total&")" return IF(HASONEVALUE('Table'[Company]),_result1, IF(ISFILTERED(Gender[Gender]),_result1, FORMAT(_totalpercent,"Percent")&"("&_total&"/"&_all&")"))Needs Improvement Associates = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Needs Improvement"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Needs Improvement"),[ID]) var _all= COUNTX(ALL('Table'),[ID]) var _percent= DIVIDE(_value,_total) var _totalpercent= DIVIDE(_total,_all) var _result= IF(_value=BLANK(),0,_percent) var _result1= FORMAT(_result,"Percent")&"("&IF(_value=BLANK(),0,_value)&"/"&_total&")" return IF(HASONEVALUE('Table'[Company]),_result1, IF(ISFILTERED(Gender[Gender]),_result1, FORMAT(_totalpercent,"Percent")&"("&_total&"/"&_all&")"))You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is exactly what i needed, THANK YOU!!!!
Hi v-robertq-msft , if i want to add another slicer into the measure you provided, do you know who i would do that? I have two slicers gender and race/ethnicity, the slicer for gender is working correctly (thanks to you), now have a separate slice for breaks out race/ethnicity (Asian, Black and Hispanic) is it possible to add the value of the slicer to the previous measure?
HI v-robertq-msft did you get a chance to look into this at all :)?
mmills2018 what are your measures? It needs a fix.
my measures are currenlty calculating correctly. I just need them updated when I apply the slicer value, but are my measures and how they are being calculated:
Total Associates = COUNT('Talent Snapshot'[Associate ID])Associates with Potential Assessment = ROUND([% Associates with Potential Assessment],2)*100 & "%" &" "& "(" & [Sum Associates with Potential Assessment] & ")"Associates with Potential = ROUND([% Associates with Potential],2)*100 & "%" &" "& "(" & [Sum Associates with Potential] & ")"Associates with High Potential = ROUND([% Associates with High Potential],2)*100 & "%" &" "& "(" & [Sum Associates with High Potential] & ")"
mmills2018 you just showed what you are displaying not how you are getting to the numbers, what is the definition of this measure:
% Associates with Potential Assessment
sorry, still learnig this, see below:
% Associates with Potential Assessment = sum('Talent Snapshot'[Count Associates with Potential Assessment])/SUM('Talent Snapshot'[Total Associates (PME)])Count Associates with Potential Assessment = (IF('Talent Snapshot'[Potential]<>"" && 'Talent Snapshot'[Total Associates (PME)]=1, 1, 0))Total Associates (PME) = if('Talent Snapshot'[Management Level]="Executive - E4"||'Talent Snapshot'[Management Level]="Executive - E3"||'Talent Snapshot'[Management Level]="Executive - E2"||'Talent Snapshot'[Management Level]="Executive - E1"||'Talent Snapshot'[Management Level]="Management - M5"||'Talent Snapshot'[Management Level]="Management - M4"||'Talent Snapshot'[Management Level]="Management - M3"||'Talent Snapshot'[Management Level]="Management - M2"||'Talent Snapshot'[Management Level]="Management - M1"||'Talent Snapshot'[Management Level]="Professional - P6"||'Talent Snapshot'[Management Level]="Professional - P5"||'Talent Snapshot'[Management Level]="Professional - P5"||'Talent Snapshot'[Management Level]="Professional - P4"||'Talent Snapshot'[Management Level]="Professional - P3"||'Talent Snapshot'[Management Level]="Professional - P2"||'Talent Snapshot'[Management Level]="Professional - P1",1,0)
Hi, mmills2018
According to your description and sample picture, I can understand the output you want to get, you want to make the total number of assessed employees not be filtered by the Gender Slicer and use it in the calculation, right? You can take a look at my steps and find if it’s useful:
This is the test data I created based on your sample form:
- I created a ‘Gender’ table for the Slicer and make sure it don’t have any relationship to the main table because if they are related, the Slicer will affect the value of measures:
- I created two measures:
Total Employees = COUNT('Table'[ID])Total Assessed Employees = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Total Assessed"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Total Assessed"),[ID]) var _percent= DIVIDE(_value,_total) return IF(_percent=BLANK(),0,_percent)Needs Improvement Associates = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Needs Improvement"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Needs Improvement"),[ID]) var _percent= DIVIDE(_value,_total) return IF(_percent=BLANK(),0,_percent)- I created a Matrix and a Slicer and place them like this:
And you can get what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks! This helped a lot. One question though, If I do not select Female or Male I want the overall total to show (both male and female), when I don't filter it to either it currently shows 0%
Hi, mmills2018
According to your description, you want to make the value of the percentage displays 100% when the Slicer is not selected, right? You can change the two measures to this:
Total Assessed Employees = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Total Assessed"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Total Assessed"),[ID]) var _value1= IF(ISFILTERED(Gender[Gender]),_value,_total) var _percent= DIVIDE(_value1,_total) return IF(_percent=BLANK(),0,_percent)Needs Improvement Associates = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Needs Improvement"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Needs Improvement"),[ID]) var _value1= IF(ISFILTERED(Gender[Gender]),_value,_total) var _percent= DIVIDE(_value1,_total) return IF(_percent=BLANK(),0,_percent)And I guess this is what you want:
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for this, but I don't want the values to be 100%, I want the values to be the combination of males and females. I only want the values to change when i filter to females. so the values should be as how they are currently being calculated, when i select female, i want the values to change.
Hi, mmills2018
According to your description, you want the total row of the table to display the combination of males and females whose status is “Total Assessed” in this column when the Slicer is not selected, right?
You can change the measure [Total Assessed Employees]:
Total Assessed Employees = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Total Assessed"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Total Assessed"),[ID]) var _all= COUNTX(ALL('Table'),[ID]) var _percent= DIVIDE(_value,_total) var _result= IF(_value=BLANK(),0,_percent) return IF(HASONEVALUE('Table'[Company])||ISFILTERED(Gender[Gender]),FORMAT(_result,"Percent"),_total)And I guess this is what you want:
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
this still is not what i want. When i dont have anything selected on the slicer, i want it to show my original calculation. so from the example you provided, when you nothing is selected on the filter it is still showing 0%, it should show the total for both male and female and then if i were to filter to female it would only show the total for the female, if i filter to male it will show the value for males. I need to select female and male, so that should show me all associates.
Hi, mmills2018
Please check if it’s what you want?
If it can meet your requirement, you can change the formula of the two measures to achieve this:
Total Assessed Employees = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Total Assessed"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Total Assessed"),[ID]) var _percent= DIVIDE(_value,_total) var _result= IF(_value=BLANK(),0,_percent) return IF(HASONEVALUE('Table'[Company]),_result,FORMAT(_result,"Percent")&"("&_total&")")Needs Improvement Associates = var _gender=SELECTEDVALUE(Gender[Gender]) var _value= COUNTX( FILTER('Table',[Gender]=_gender&&[Total Assessed /Needs Improvement ]="Needs Improvement"), [ID]) var _total=COUNTX(FILTER('Table',[Total Assessed /Needs Improvement ]="Needs Improvement"),[ID]) var _percent= DIVIDE(_value,_total) var _result= IF(_value=BLANK(),0,_percent) return IF(HASONEVALUE('Table'[Company]),_result,FORMAT(_result,"Percent")&"("&_total&")")And you can get what you want.
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for this, but i am not sure what is different from what you previously showed me. for total assessed, it should not show 0% when male or female is NOT selected. I want it to show a total of 53% (8/15) when male or female is not selected, so i want to know of my 15 total associates how many were assessed. The slicer for male and female should only show me of those assessed how many were male or female, so when i select male it should then show 75%.
