I am trying to build a Diversity report for my organization.
A simple example of the data I work with:
Candidate IDGenderOrg lvl1Org lvl 2Org lvl2ShortlistedInterviewedOffered
| 1 | Male | Sport | Squash | Ball | Yes | Yes | Yes |
| 2 | Female | Sport | Squash | Ball | Yes | Yes | Yes |
| 3 | Unknown | Sport | Squash | Ball | Yes | Yes | Yes |
I added an excel file with the above and a power PI report here:
https://wetransfer.com/downloads/e15fd9ac80e4da54360195bf5c5552c420221125151306/8f48a8bb5a0a74c98be0...
I need to show the diversity data for each characteristic Broken down for each recruitment step (shortlisted, Interview, Offer).
As we cannot use measures in the x Axis of a graph
I started by Adding a new column (column1) to use as reference: with each recruitment step.
Using Dax I then created the below measure:
Measure test =
VAR Short = CALCULATE(
SUM( Table1[Candidate Sum] ),
Table1[Shortlisted] ="Yes")
VAR Inte = CALCULATE(
SUM( Table1[Candidate Sum]),
Table1[Interviewed] ="Yes")
VAR Offe = CALCULATE(
SUM (Table1[Candidate Sum]),
Table1[Offered] = "Yes")
RETURN
SWITCH(
SELECTEDVALUE( 'Recrtuiment steps'[Column1]),
"Shortlisting", Short,
"Interview", Inte ,
"Offer", Offe
)
This returns the Diversity data perfectly https://ibb.co/mtdDFtn
Now my issue is that I need to stop the graph from showing the legend data (Gender) when the value is less than 5. The problem is that the legend cannot accept a value.
Adding a if less than 5 works in removing the data:
less than 5 test =
VAR Short = CALCULATE(
SUM( Table1[Candidate Sum] ),
Table1[Shortlisted] ="Yes")
VAR Inte = CALCULATE(
SUM( Table1[Candidate Sum]),
Table1[Interviewed] ="Yes")
VAR Offe = CALCULATE(
SUM (Table1[Candidate Sum]),
Table1[Offered] = "Yes")
RETURN
SWITCH(
SELECTEDVALUE( 'Recrtuiment steps'[Column1]),
"Shortlisting", IF(Short < 5 ,Blank() , Short) ,
"Interview", IF(Inte <5 , Blank() , Inte) ,
"Offer", IF( Offe < 5 , Blank() , Offe )
)
This DAX will remove the data completely if less than 5. Good start but now it looks like we don't have any small numbers instead of hiding them. https://ibb.co/SBYQQ1w
What I need is for the legend to be amended to merge/group categories that are less than into a new "Hidden" value.
So instead of (picturing the graph):
| Non Binary 3% - 3 |
| Unknown 2% - 2 |
| Male 50% - 50 |
| Female 45% -45 |
| shortlist |
I currently get:
| |
| |
| Male 50% - 50 |
| Female 47% -45 |
| shortlist |
I need:
| |
| Hidden 5% |
| Male 50% - 50 |
| Female 45% -45 |
| shortlist |
Is this possible, or am I not using the correct approach?
My set of raw data is bigger but I only selected this to understand the logic. Thank you
