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.

MFelix's avatar
MFelix
Icon for Super User rankSuper 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
    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.