Creating charts using Pivot data and CountA and CountIF from Excel

[Mal_Sondh]

Hi All,

I have the following data (Project Table):

Project No

Project Name

Project Description

IT Owner

Business Owner

Project Stage

Country

Division

Project Start Date

Project Completion Date

% Complete

Missed Chance

Business Efficiency

Demand

Risk

Improved Quality

Communication

Knowledge

Responsive

Expertise

Value

1

A

AA

JK

RS

Initial

UK

Product

01/12/2019

14/10/2020

Late

Y

Y

Y

3

2

3

5

4

2

B

BB

JK

RS

In Progress

Finland

Product

01/01/2020

14/12/2020

0%

Late

Y

Y

Y

4

3

3

4

5

3

C

CC

JK

TT

In Progress

China

Product

01/12/2019

14/12/2020

30%

Y

Y

Y

5

4

3

5

4

4

D

DD

JK

TT

In Progress

UK

Product

01/12/2019

14/10/2020

25%

Y

Y

Y

5

2

3

4

4

5

E

EE

TZ

RS

In Progress

Belgium

Infra

01/12/2019

14/10/2020

65%

Late

Y

4

2

4

5

4

6

F

FF

TZ

EB

In Progress

Italy

Product

01/12/2018

14/10/2020

86%

Late

Y

4

2

5

4

4

7

G

GG

TZ

EB

In Progress

UK

Wealth

01/12/2019

14/10/2020

92%

Y

4

2

2

5

2

8

H

HH

TZ

EB

In Progress

UK

Infra

01/12/2019

14/10/2020

10%

Y

Y

4

1

4

5

9

I

II

TZ

SM

In Progress

UK

Infra

01/12/2019

14/10/2020

27%

Late

Y

Y

5

1

5

10

J

JJ

TZ

SM

Complete

UK

Infra

01/12/2019

14/12/2020

100%

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 Work	10	=COUNTA(A2:A11)
Live Projects	8	=COUNTIF(K2:K11,"<100%")
Delivered Projects	1	=COUNTIF(K2:K11,"=100%")
Missed Chance	5	=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.

[v-shex-msft · Accepted solution]

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

[HotChilli]

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]

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.