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.
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) |
Solved! Go to Solution.
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.
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.
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?
This is exactly what i needed, THANK YOU!!!!
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%.
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
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 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:
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)
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%
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
sorry, still learnig this, see below:
@mmills2018 what are your measures? It needs a fix.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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:
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |