Funnel chart view with custom columns

[saipawar]

Hi, I need help with a case statement such that I can use the calculated measure to create a funnel visual - 

Dummy Data - 

Team	Req Status	Interviews in process	Hires
A	Open	3	0
B	Open	2	0
A	Closed	0	1
C	Open	1	0
A	Open	5	0
B	Closed	0	1

FYI :

When Status is Closed, Hires = 1 and Open = 0

Desired Output - 

Team	Total Approved (Open+Closed)	In Progress (Only Open)	Interviews in Process	Hires
A	3	2	8	1
B	2	1	2	1
C	1	1	1	0

Lastly, I'd like to plot the desired output table on a funnel chart.

1.Total approved

2.Reqs In progress

3. Interviews in Process

4. Hired

[v-yuaj-msft · Accepted solution]

Hi @saipawar ,

Based on your description, you can create a calculated table as follows.

Table 2 =

SUMMARIZE (

    'Table',

    [Team],

    "Total approved", COUNTROWS ( 'Table' ),

    "Reqs In progress",

        0+ COUNTROWS ( FILTER ( 'Table', [Req Status] = "Open" ) ),

    "Interviews in Process", SUM ( 'Table'[Interviews in process] ),

    "Hired", SUM ( 'Table'[Hires] )

)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[amitchandak]

@saipawar , Try measures like

Approved= Sumx(filter(Table, Table[Status] in {"Open","Closed"}), Table[Interviews ])

In Progress= Sumx(filter(Table, Table[Status] in {"Open"}), Table[Interviews ])

in process Hires = sum(Table[in process Hires])