Forum Discussion

thorn1831's avatar
thorn1831
Regular Visitor
6 years ago
Solved

Project Conversion %

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)....
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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.

thorn1831's avatar
thorn1831
Regular Visitor
6 years ago

Thanks MFelix, 

 

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

 

ChartExcel Formula
Feasibility StudyCount of Feasibility Study/ Count of Initial Meetings
Feasibility DeliveredCount of Feasibility Delivered/ Count of Feasibility Study
Turned into RFP/RFQCount of RFPRFQ/ Count of Feasibility Delivered 
Selected/ In IGACount of Selected/ Count of RFPRFQ
Sold/Closed Count of Contract Executed / Count of Selected

 

And here is some sample data in table format. 

 

ProjectIDInitial MeetingsFeasibility StudyFeasibility DeliveredRFPRFQSelectedContract Executed
2TRUETRUETRUETRUETRUETRUE
3TRUETRUETRUETRUETRUETRUE
4TRUETRUETRUETRUETRUETRUE
5TRUETRUETRUETRUETRUETRUE
6TRUEFALSETRUETRUETRUETRUE
7TRUETRUETRUEFALSEFALSEFALSE
8TRUETRUETRUETRUETRUEFALSE
9TRUETRUETRUETRUEFALSEFALSE
10TRUETRUETRUEFALSEFALSEFALSE
11TRUETRUETRUEFALSEFALSEFALSE
12TRUETRUETRUETRUETRUEFALSE
13TRUETRUETRUEFALSEFALSEFALSE
14TRUETRUETRUEFALSEFALSEFALSE
15TRUETRUETRUETRUEFALSEFALSE
16TRUETRUETRUEFALSEFALSEFALSE
17TRUETRUETRUETRUETRUEFALSE
18TRUETRUETRUEFALSEFALSEFALSE
19TRUETRUETRUEFALSEFALSEFALSE
20TRUETRUEFALSEFALSEFALSEFALSE
MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

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.