Calculate percentage of specific status out of several

[Typhoon74]

Hi everyone,

I am starting with power bi desktop and stucking finding a solution for the following senario.

I do have a list of items having a status per department.

On a Matrix I would like to add a column showing the % open by department calculated by the SUM of Status '07 Finished' and '08 Report Ready' divided by the sum of status 07, 08, 09 and 12.

A excel file with sample data can be looked at here 

The Result Matrix should look like this showing all status columns showing the numbers and the new column showing the percentage open.

Thanks in advance for all your helpful suggestions and many greetings

Dirk

[v-zhangti · Accepted solution]

Hi, @Typhoon74 

You can try the following methods.

%Open = 
VAR N1 =
    CALCULATE (
        COUNT ( 'Table'[STATUS] ),
        FILTER ( 'Table', [STATUS] = "07 Finished" )
    )
VAR N2 =
    CALCULATE (
        COUNT ( 'Table'[STATUS] ),
        FILTER ( 'Table', [STATUS] = "08 Report Ready" )
    )
VAR N3 =
    CALCULATE (
        COUNT ( 'Table'[STATUS] ),
        FILTER ( 'Table', [STATUS] = "09 Written" )
    )
VAR N4 =
    CALCULATE (
        COUNT ( 'Table'[STATUS] ),
        FILTER ( 'Table', [STATUS] = "12 Archived" )
    )
RETURN
    DIVIDE ( N1 + N2, N1 + N2 + N3 + N4 )

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Typhoon74 · Accepted solution]

Finally I got my visual created with the help of @v-zhangti reply and other ideas I found out in this community.

I build my visual with measures 

1. I created a measure to count the Qty per each Status
   
2. For the Total STATUS I am using the measure
   
3. And for Open %, I used the solution provided by v-zhangti

[v-zhangti · Accepted solution]

Hi, @Typhoon74 

You can try the following methods.

%Open = 
VAR N1 =
    CALCULATE (
        COUNT ( 'Table'[STATUS] ),
        FILTER ( 'Table', [STATUS] = "07 Finished" )
    )
VAR N2 =
    CALCULATE (
        COUNT ( 'Table'[STATUS] ),
        FILTER ( 'Table', [STATUS] = "08 Report Ready" )
    )
VAR N3 =
    CALCULATE (
        COUNT ( 'Table'[STATUS] ),
        FILTER ( 'Table', [STATUS] = "09 Written" )
    )
VAR N4 =
    CALCULATE (
        COUNT ( 'Table'[STATUS] ),
        FILTER ( 'Table', [STATUS] = "12 Archived" )
    )
RETURN
    DIVIDE ( N1 + N2, N1 + N2 + N3 + N4 )

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbriga]

Let's try:

Open%=
VAR _Finished = CALCULATE(COUNTROWS(Table), KEEPFILTERS (Table[Status] = "07 Finished")
VAR _Ready = CALCULATE(COUNTROWS(Table), KEEPFILTERS (Table[Status] = "08 Report Ready")
VAR _Written= CALCULATE(COUNTROWS(Table), KEEPFILTERS (Table[Status] = "09 Written")
VAR _Archived= CALCULATE(COUNTROWS(Table), KEEPFILTERS (Table[Status] = "12 Archived")
Return
DIVIDE(_Finished+_Ready , _Finished + _Ready +_Written+_Archived)

Does it help?

[Typhoon74]

@rbriga  It is giving me a error message

"Wrong Syntax for "Return" any idea or suggestion what to do?

[rbriga]

Remove the commas at the end of each VAR

[amitchandak]

@Typhoon74 , You can get open % like

divide(countrows(filter(allselected(Table), Table[Deaprtment] = max(Table[Deaprtment]) && table[Status] in{"07 Finished","08 Report Ready"})),countrows(filter(allselected(Table), Table[Deaprtment] = max(Table[Deaprtment]) && table[Status] in{"07 Finished","08 Report Ready","09 Written","12 Archived"})) )

But you will not get that display

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

[Typhoon74]

Thanks amitchandak, it provides me a table showing the percentage by department. Will vore for the option of hybrid tables