Project Conversion %

[thorn1831]

Hey all,

Having some issues converting an old Excel report into PowerBI. I have 6 project stages (Initial Meetings, Feasibility Study, Feasibility Delivered, RFPRFQ, Selected, Contract Executed). What I need to show (% Converted) is how many projects moved on to the next stage.

Here is what I'm trying to re-create. Ideally this would be combined into one table/chart combo with a dropdown filter based on the conversion stage controlling what formula is shown as "% converted". 

Existing Excel Formulas:

Raw Data:

Thanks for the help!

[Icey]

Hi @thorn1831 ,

Is this problem solved?

Best Regards,

Icey

[MFelix]

Hi @thorn1831 ,

Not really understanding your data and the excel calculations you are making in order to transpose that into DAX.

Looking at the first chart you have Sold/Closed what are the columns in your data that you are using to calculated it (what are the stages consider for those changes)?

Regarding the calculations you present on the excel format basically you would need to do something similar to this:

Feasibility Study = CALCULATE(COUNT(Table[ProjectID]);Table[Feasability Study] = "TRUE") / CALCULATE(COUNT(Table[ProjectID]);Table[Initial Meetings] = "TRUE") 

This need to be adapt, but  we need to look at your data and check the way you need to calculated things

Can you also share a table format or a PIBX file with data since it's a lot of information to make the input by hand.

Please see this post How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

[thorn1831]

Thanks @MFelix, 

Sorry that wasn't clear. Here's how those charts were calculated. 

Chart	Excel Formula
Feasibility Study	Count of Feasibility Study/ Count of Initial Meetings
Feasibility Delivered	Count of Feasibility Delivered/ Count of Feasibility Study
Turned into RFP/RFQ	Count of RFPRFQ/ Count of Feasibility Delivered
Selected/ In IGA	Count of Selected/ Count of RFPRFQ
Sold/Closed	Count of Contract Executed / Count of Selected

And here is some sample data in table format. 

ProjectID	Initial Meetings	Feasibility Study	Feasibility Delivered	RFPRFQ	Selected	Contract Executed
2	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
3	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
4	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
5	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
6	TRUE	FALSE	TRUE	TRUE	TRUE	TRUE
7	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
8	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
9	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
10	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
11	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
12	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
13	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
14	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
15	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
16	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
17	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE
18	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
19	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE
20	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE

[MFelix · Accepted solution]

Hi  @thorn1831 ,

you need to create the following measures:

Feasability deliver =
DIVIDE (
    CALCULATE (
        COUNT ( Projects[ProjectID] );
        Projects[Feasibility Delivered] = TRUE ()
    );
    CALCULATE (
        COUNT ( Projects[ProjectID] );
        Projects[Feasibility Study] = TRUE ()
    )
)


Feasability Study =
DIVIDE (
    CALCULATE (
        COUNT ( Projects[ProjectID] );
        Projects[Feasibility Study] = TRUE ()
    );
    CALCULATE (
        COUNT ( Projects[ProjectID] );
        Projects[Initial Meetings] = TRUE ()
    )
)


Selected/ In IGA =
DIVIDE (
    CALCULATE ( COUNT ( Projects[ProjectID] ); Projects[Selected] = TRUE () );
    CALCULATE ( COUNT ( Projects[ProjectID] ); Projects[RFPRFQ] = TRUE () )
)


Sold/Closed =
DIVIDE (
    CALCULATE (
        COUNT ( Projects[ProjectID] );
        Projects[Contract Executed] = TRUE ()
    );
    CALCULATE ( COUNT ( Projects[ProjectID] ); Projects[Selected] = TRUE () )
)

Turned into RFP/RFQ =
DIVIDE (
    CALCULATE ( COUNT ( Projects[ProjectID] ); Projects[RFPRFQ] = TRUE () );
    CALCULATE (
        COUNT ( Projects[ProjectID] );
        Projects[Feasibility Delivered] = TRUE ()
    )
)

Format measure as % and place them on a visualization of your choice, be aware that this being a measure will calculate at the level you are on the visualization so if you add state it will show state level if there is no level (as Ihave in the PBIX file) it show full data values.

Check PBIX file attach.