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). 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!

 

 

 

 

 

 

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.

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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'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
        Super 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.