PowerBI percentage by count of another column

user_7258989 0
Viewed 166

How do I calculate the percentage of values in column A from the count of column B? Or the percentage of non-blank values from count of all values (blank and not)?

I have a column with unique IDs and a 4 columns with values, and I want to see the 4 columns together in a bar chart which I can do with count.

ID A B C D
12345 1 3
12346 1 2 3 4
12347 3 4
12348 3 4

With count it would show as A=2, B=1, C=4, D=3. In the percentage I'm looking for it would show as A=50%, B=25%, C=100%, D=75%.

Thanks! Maya

powerbi
2 Answers

Achievable with following measures

_a = 
DIVIDE (
    CALCULATE ( COUNT ( tbl[A] ), ALL ( tbl[ID] ) ),
    CALCULATE ( COUNT ( tbl[ID] ), ALL ( tbl[ID] ) )
)

_b = 
DIVIDE (
    CALCULATE ( COUNT ( tbl[B] ), ALL ( tbl[ID] ) ),
    CALCULATE ( COUNT ( tbl[ID] ), ALL ( tbl[ID] ) )
)

_c = 
DIVIDE (
    CALCULATE ( COUNT ( tbl[C] ), ALL ( tbl[ID] ) ),
    CALCULATE ( COUNT ( tbl[ID] ), ALL ( tbl[ID] ) )
)

_d = 
DIVIDE (
    CALCULATE ( COUNT ( tbl[D] ), ALL ( tbl[ID] ) ),
    CALCULATE ( COUNT ( tbl[ID] ), ALL ( tbl[ID] ) )
)

Solution

user_111561310

Try this approach:

Create 4 formulas

A% = CONCATENATE(FORMAT((CALCULATE(COUNT(Sheet5[A]),ALLNOBLANKROW(Sheet5[A]))/COUNTROWS(ALL(Sheet5)))*100,"") , "%")

B% = CONCATENATE(FORMAT((CALCULATE(COUNT(Sheet5[B]),ALLNOBLANKROW(Sheet5[B]))/COUNTROWS(ALL(Sheet5)))*100,"") , "%")

C% = CONCATENATE(FORMAT((CALCULATE(COUNT(Sheet5[C]),ALLNOBLANKROW(Sheet5[C]))/COUNTROWS(ALL(Sheet5)))*100,"") , "%")

D% = CONCATENATE(FORMAT((CALCULATE(COUNT(Sheet5[D]),ALLNOBLANKROW(Sheet5[D]))/COUNTROWS(ALL(Sheet5)))*100,"") , "%")

End result is:

enter image description here

user_22935600
Related