Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Nicoworkingdude
New Member

Diversity report - Using Measure in Legend of graph

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

1MaleSportSquashBallYesYesYes
2FemaleSportSquashBallYesYesYes
3UnknownSportSquashBallYesYesYes

 

 

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

1 REPLY 1
lbendlin
Super User
Super User

You cannot really create dynamic buckets.  They have to be predefined in a refererence table.  Then you can use the column from that table as the legend, and create your measures around it.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.