cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
supergallager34
Frequent Visitor

Percentage of TOTAL measure with slicer for unpivot column

Hi, 

I am trying to calculate the percentage of the total for an unpivot column that will be coming along with a slicer. I am pretty new to power bi so this may possibly be a pretty easy one. However, I am having trouble trying to make it work. Any help would be appreciated. 

I have a  sample table, 

EIDGenderCompanyBilingual
456MaleACan Speak two languages
123FemaleACan speak one language
097MaleBCan Speak two languages
678FemaleBCan Speak two languages
345MaleBCan speak one language
234MaleACan speak one language
098FemaleACan Speak two languages
786FemaleACan speak one language
546FemaleBCan Speak two languages
874FemaleBCan Speak two languages
012MaleACan Speak two languages
023MaleACan speak one language
056MaleBCan speak one language
604FemaleBCan Speak two languages

 
I did an unpivot on the columns gender and bilingual, 

EIDCompanyUnpivot
456AMale
456ACan Speak two languages
123AFemale
123ACan speak one language
097BMale
097BCan Speak two languages
678BFemale
678BCan Speak two languages
345BMale
345BCan speak one language
234AMale
234ACan speak one language
098AFemale
098ACan Speak two languages
786AFemale
786ACan speak one language
546BFemale
546BCan Speak two languages
874BFemale
874BCan Speak two languages
012AMale
012ACan Speak two languages
023AMale
023ACan speak one language
056BMale
056BCan speak one language
604BFemale
604BCan Speak two languages

 Afterward, I created a table that looks like this, 

 
 
 

Capture.JPG
As you can see, my "%" measure is incorrect when I select it for just one company. However, when I select both the company, it shows correctly. 
Capture.JPG

My code for the measure is the following: 

% = CALCULATE(DIVIDE(DISTINCTCOUNT('Table'[Employee ID]),CALCULATE(DISTINCTCOUNT('Table'[Employee ID]),ALLSELECTED())))


Is there a way to create a measure that would dynamically work based on the filter? Please let me know. 









3 REPLIES 3
Greg_Deckler
Super User
Super User

@supergallager34 - As I suspected, not unpivoting makes things much easier. You can use a matrix and "Show as percentage of grand total". See if the attached PBIX is acceptable. Below sig. Has all of the information and numbers should be accurate. Table (7) and Page 7.

 

 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Gregg, 
 I have taken your suggestion and did the unpivot which seemed to make it easier since I would need to create calculations with the percentage of grand total. 
However, my calculation also consists of a date slicer. And the table originally contains enter and leave dates. I have inactive relationships with the enter and leave dates with a date table. And my codes then are, 

% = CALCULATE(CALCULATE(DIVIDE(DISTINCTCOUNT('Table'[Employee ID]),CALCULATE(DISTINCTCOUNT('Table'[Employee ID]),ALLSELECTED())), 
FILTER(VALUES('Table'[Enter_Date]),'Table'[Enter_Date] <= MAX('Date'[Dates])),

FILTER(VALUES('Table'[Leave_Date]),'Table'[Leave_Date] >= MIN('Date'[Dates]))). 

When I use this formula, my percentage of grand totals seems to show wrong percentages.

Greg_Deckler
Super User
Super User

@supergallager34 - Should be and I will have to take a closer look but off the top of my head, it bugs me that you unpivoted those columns, that would not have been my first choice.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!