Forum Discussion

Mal_Sondh's avatar
Mal_Sondh
Helper II
5 years ago
Solved

Creating charts using Pivot data and CountA and CountIF from Excel

Hi All,

 

I have the following data (Project Table):

Project NoProject NameProject DescriptionIT OwnerBusiness OwnerProject StageCountryDivisionProject Start DateProject Completion Date% CompleteMissed ChanceBusiness EfficiencyDemandRiskImproved QualityCommunicationKnowledgeResponsiveExpertiseValue
1AAAJKRSInitialUKProduct01/12/201914/10/2020 LateY YY32354
2BBBJKRSIn ProgressFinlandProduct01/01/202014/12/20200%Late YYY43345
3CCCJKTTIn ProgressChinaProduct01/12/201914/12/202030% Y YY54354
4DDDJKTTIn ProgressUKProduct01/12/201914/10/202025%  YYY52344
5EEETZRSIn ProgressBelgiumInfra01/12/201914/10/202065%LateY   42454
6FFFTZEBIn ProgressItalyProduct01/12/201814/10/202086%Late Y  42544
7GGGTZEBIn ProgressUKWealth01/12/201914/10/202092% Y   42252
8HHHTZEBIn ProgressUKInfra01/12/201914/10/202010%  Y Y4 145
9IIITZSMIn ProgressUKInfra01/12/201914/10/202027%LateY  Y5 15 
10JJJTZSMCompleteUKInfra01/12/201914/12/2020100%  Y Y    

 

 

 

 

From this data in excel I have created the following so that I can then create a chart.  I would like to have the ability to drill back to the underlying data:

Sub table - Book of Work Excel Formula
Current Book of Work10=COUNTA(A2:A11)
Live Projects8=COUNTIF(K2:K11,"<100%")
Delivered Projects1=COUNTIF(K2:K11,"=100%")
Missed Chance5=COUNTIF(L2:L11,"Late")

 

What is the best approach for this in Power BI?

 

I have tried the following:

I have reated a reference table in the transform data area, highlighted, Project No, % Complete, and Missed Chance.  I then unpivoted the data which flattened the data, however i can get the count of projects (Current Book of Work) to be correct, however i cannot get the other values correct.  In addition doing it this way i dont think i can drill to the underlying data.

 

Any assistance on this would be appreciated, i have attached an excel version of the formulas and the charts that are needed.

 

Thanks in advance.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Mal_Sondh,

    I think duplicate and unpivoted raw table value fields should be a great idea to reduce the formula complexity.

    After these steps, you can use the switch function to switch different calculation expressions based on the current category type. (filter on current category and specific attribute fields type with conditions)

    Measure =
VAR currCate =
    SELECTEDVALUE ( Table[Category] )
RETURN
    SWITCH (
        currCate,
        "Current Book of Work", COUNTROWS ( VALUES ( Table[Project No] ) ),
        "Live Projects",
            CALCULATE (
                COUNTROWS ( Table ),
                FILTER (
                    ALLSELECTED ( Table ),
                    [Attribute] = "% Complete"
                        && VALUE ( Table[Value] ) < 1
                )
            ),
        "Delivered Projects",
            CALCULATE (
                COUNTROWS ( Table ),
                FILTER (
                    ALLSELECTED ( Table ),
                    [Attribute] = "% Complete"
                        && VALUE ( Table[Value] ) = 1
                )
            ),
        "Missed Chance",
            CALCULATE (
                COUNTROWS ( Table ),
                FILTER (
                    ALLSELECTED ( Table ),
                    [Attribute] = "%Missed Change"
                        && Table[Value] = "Late"
                )
            )
    )

    Regards,

    Xiaoxin Sheng

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    Leave the data as it is.

    I'm pretty sure you can write measures to get all the results you want.

    Start with this:

    ProjectCount = COUNT(Table[Project No])

    The other measures can use this - just add filter clauses e.g.

    Live Projects = CALCULATE([ProjectCount], Table[% Complete] < 1)

     

     

    The Average and Min measures - You will have to write measures for each column i.e. communication

    • Mal_Sondh's avatar
      Mal_Sondh
      Helper II
      5 years ago

      Thanks HotChilli I dont think measures will do it as the measures can only be used in the chart values and not in the Axis or Legend.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Mal_Sondh,

    I think duplicate and unpivoted raw table value fields should be a great idea to reduce the formula complexity.

    After these steps, you can use the switch function to switch different calculation expressions based on the current category type. (filter on current category and specific attribute fields type with conditions)

    Measure =
VAR currCate =
    SELECTEDVALUE ( Table[Category] )
RETURN
    SWITCH (
        currCate,
        "Current Book of Work", COUNTROWS ( VALUES ( Table[Project No] ) ),
        "Live Projects",
            CALCULATE (
                COUNTROWS ( Table ),
                FILTER (
                    ALLSELECTED ( Table ),
                    [Attribute] = "% Complete"
                        && VALUE ( Table[Value] ) < 1
                )
            ),
        "Delivered Projects",
            CALCULATE (
                COUNTROWS ( Table ),
                FILTER (
                    ALLSELECTED ( Table ),
                    [Attribute] = "% Complete"
                        && VALUE ( Table[Value] ) = 1
                )
            ),
        "Missed Chance",
            CALCULATE (
                COUNTROWS ( Table ),
                FILTER (
                    ALLSELECTED ( Table ),
                    [Attribute] = "%Missed Change"
                        && Table[Value] = "Late"
                )
            )
    )

    Regards,

    Xiaoxin Sheng