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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors