Percentage of TOTAL measure with slicer for unpivot column

0
votes

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,

EID Gender  Company Bilingual
456 Male    A   Can Speak two languages
123 Female  A   Can speak one language
097 Male    B   Can Speak two languages
678 Female  B   Can Speak two languages
345 Male    B   Can speak one language
234 Male    A   Can speak one language
098 Female  A   Can Speak two languages
786 Female  A   Can speak one language
546 Female  B   Can Speak two languages
874 Female  B   Can Speak two languages
012 Male    A   Can Speak two languages
023 Male    A   Can speak one language
056 Male    B   Can speak one language
604 Female  B   Can Speak two languages

I did an unpivot on the columns gender and bilingual,

EID Company Unpivot
456 A   Male
456 A   Can Speak two languages
123 A   Female
123 A   Can speak one language
097 B   Male
097 B   Can Speak two languages
678 B   Female
678 B   Can Speak two languages
345 B   Male
345 B   Can speak one language
234 A   Male
234 A   Can speak one language
098 A   Female
098 A   Can Speak two languages
786 A   Female
786 A   Can speak one language
546 B   Female
546 B   Can Speak two languages
874 B   Female
874 B   Can Speak two languages
012 A   Male
012 A   Can Speak two languages
023 A   Male
023 A   Can speak one language
056 B   Male
056 B   Can speak one language
604 B   Female
604 B   Can 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.

2
powerbidax
You can just add your "Count of Employeee" column a second time and select via Right click "Show as Percentage". If you are still interested to solve it via measure let me know and I will have a look. – VBA Pete
Why/How Gender and Language details in the same column of a table? – mkRabbani

2 Answers

1
votes

Try the following measure:

% = DIVIDE(COUNTA('Table'[Value]),COUNTROWS(Table))

Note that 'Table'[Value] refers to the column, where you store gender and language.

0
votes

Not sure why your are mixing Gender and Bilingual values in one single column of a table visual. But if it is a requirement, you are missing a required column (for standard calculation) where the value Gender and Bilingual will be exist. The result of your unpivot should looks like below where the "Attribute" column is missing your case-

enter image description here

Now create these following 3 measure-

1.

value_count = COUNT(your_table_name[Value])

2.

value_count_total = 

CALCULATE(
    COUNT(your_table_name[Attribute]),
    FILTER(
        ALLSELECTED(your_table_name),
        your_table_name[Attribute] = MIN(your_table_name[Attribute])
    )
)

3.

value_percentage = [value_count]/[value_count_total]

Convert the 3rd measure type as Percentage and the final output of your table visual should be as below-

enter image description here