Dashboard Issue

[tai87]

Hi everyone. This is my 1st time using Power BI. I have over 600 contracts to be monitored. I wanted to have a automated dashboard for this purpose. I managed to draft a simple dashboard based on fake data to illustrate the information required for the dashboard.

Scenario : 12 contracts - 8 active + 4 expired

Dashboard requirement :

• Automatic calculation of duration to expiry (I managed to do this using today() and datedif() function in excel)
• 2 sections - active and expired
• Ability to filter based on division, contract value, service type, duration to expiry and appointment strategy
• Ability to display list of contract, total amount and number of contract based on selection 

Below are the issues I faced:

1)  The slicer is did not sort the number based on its value. I was sorted based on the first digit in the number.

2) I could not exclude Expired contract from the active contract. There should only be 8 contracts in green (active) section when all the fields in the slicer is cleared. I tried filtering the slicer under "Filters on this visual" dropdown, but it only removes "Expired" from the slicer. The data displayed in both "Contract Value" and "Contracts" card remains the same.

Appreciate if anyone can help me solve this.

I tried to attach the .xlsx and .pbix files I use for the above scenario but somehow it is not supported. 

Thank you in advance!

[BA_Pete · Accepted solution]

Hi @tai87 ,

1) The values are sorting alphabetically as the column you are using is of Text data type. You either need to remove the "Expired" value from this column and convert it to a numerical data type, or you need to add a new Sort Key column that is a numerical data type that you can sort this column by.

2) In order to be able to display both Active and Expired contracts side-by-side on your page, the simplest thing to do would be to create role-playing measures. You would create a new column in your fact table that categorises contracts as either "Active" or "Expired" based on your own business logic, then reference this column in your measures. For example:

_activeContractValue =
CALCULATE(
    SUM(yourTable[ContractValue]),
    yourTable[contractCategory] = "Active"
)

_expiredContractValue =
CALCULATE(
    SUM(yourTable[ContractValue]),
    yourTable[contractCategory] = "Expired"
)

You could then display a card on each side of your report page for each of these values. They would each calculate independently of the other, and also both react/update based on your slicer selections.

Pete

[tai87]

Hi @BA_Pete ,

I tried both your suggestion. It works great. Thank you so much!

[tai87]

Hi @BA_Pete ,

Thank you so much for your prompt reply. 

Let me see if I understand this correctly.

1) To sort my slicer in numerical sequence, I need to change my data type for the "EXPIRES IN (MONTH)" column in source .xlsx file to Number.

2) To segregate the dashboard based on contract status, I need to classify the contract as "Active" and "Expired" in the source .xlsx file. I attached below the table from Excel that I use for the sample dashboard.

DIVISION

CONTRACT NAME

SERVICE TYPE

VENDOR

APPOINTMENT STRATEGY

CONTRACT VALUE (RM)

CONTRACT VALUE RANGE

CONTRACT SUM PER YEAR (RM)

DURATION (YEAR)

START DATE

END DATE

EXPIRY YEAR

Control Column (Today's Date) =TODAY()

Control Column (Duration to Expiry) =DATEDIF(today's date,end date,"M")

EXPIRES IN (MONTH) =IF(ISNUMBER(N2),N2,"Expired")

Engineering

Maintenance and Servicing of Fire Extinguisher

Maintenance

Company A

Open Tender

RM                        600,000.00

Between 500k to 5mil

200,000.00

3

15/07/2019

14/07/2022

2022

15/06/2022

0

0

Administration

Supply And Deliver of Pre-Printed Smartcard and Related Consumables

Supply

Company B

Open Tender

RM                        250,000.00

500k and below

83,333.33

3

15/07/2019

14/07/2022

2022

15/06/2022

0

0

Support Services

Aircraft Noise Monitoring and Air Pollutant Index

Service

Company C

Open Tender

RM                        300,000.00

500k and below

100,000.00

3

01/11/2018

31/10/2021

2021

15/06/2022

#NUM!

Expired

Engineering

Building Maintenance (Architectural, Civil & Structural) of Ancillary Buildings

Maintenance

Company D

Open Tender

RM                   78,000,000.00

More than 10 mil

26,000,000.00

3

1/2/2020

31/1/2023

2023

15/06/2022

07

7

IT

Calibration Service and Supply & Installation of Spare Parts for  Automate Barcode Reader (BCR)

Supply, Install & Calibrate

Company F

Direct Award

RM                     1,000,000.00

Between 500k to 5mil

333,333.33

3

1/9/2019

31/8/2022

2022

15/06/2022

02

2

Engineering

Comprehenive Maintenance of 10 units of Elevator at Wing A

Maintenance

Company G

Direct Award

RM                   35,000,000.00

More than 10 mil

8,750,000.00

4

1/1/2020

31/12/2023

2023

15/06/2022

18

18

Engineering

Comprehensive Maintenance for People Mover

Maintenance

Company H

Direct Award

RM                   30,000,000.00

More than 10 mil

10,000,000.00

3

1/10/2021

30/9/2024

2024

15/06/2022

27

27

Engineering

Comprehensive Maintenance Contract for Escalators

Maintenance

Company I

Direct Award

RM                   15,000,000.00

More than 10 mil

5,000,000.00

3

1/7/2021

30/6/2024

2024

15/06/2022

24

24

Security

Comprehensive Maintenance of Screening Machine at Wing A

Maintenance

Company J

Direct Award

RM                   20,000,000.00

More than 10 mil

6,666,666.67

3

1/1/2020

31/12/2022

2022

15/06/2022

06

6

Engineering

Comprehenive Maintenance of 20 units of Elevator at Wing B

Maintenance

Company K

Direct Award

RM                   15,000,000.00

More than 10 mil

7,500,000.00

2

1/2/2020

31/1/2022

2022

15/06/2022

#NUM!

Expired

Engineering

Comprehensive Maintenance Services People Mover and Dumbwaiters at Wing C

Maintenance

Company L

Direct Award

RM                   10,000,000.00

Between 5mil to 10mil

3,333,333.33

3

1/4/2019

31/3/2022

2022

15/06/2022

#NUM!

Expired

Security

Comprehensive Operation & Maintenance of Screening System at Wing B

Operation & Maintenance

Company M

Direct Award

RM                   25,000,000.00

More than 10 mil

8,333,333.33

3

1/5/2019

30/4/2022

2022

15/06/2022

#NUM!

Expired

Since my actual data has more than 600 contracts, I would not want to scrutinize the list on daily basis to see which contract has reached expiry. 

I found a way to automate this by adding 2 control columns. The first one will update today's date and the other will calculate the duration to expiry.

From my understanding of your suggestion in item (2), my new table should be like this:

DIVISION

CONTRACT NAME

SERVICE TYPE

VENDOR

APPOINTMENT STRATEGY

CONTRACT VALUE (RM)

CONTRACT VALUE RANGE

CONTRACT SUM PER YEAR (RM)

DURATION (YEAR)

START DATE

END DATE

EXPIRY YEAR

Today's Date

EXPIRES IN (MONTH)

EXPIRES IN (MONTH) =IF(ISNUMBER(N18),"Active","Expired")

Engineering

Maintenance and Servicing of Fire Extinguisher

Maintenance

Company A

Open Tender

RM                        600,000.00

Between 500k to 5mil

200,000.00

3

15/07/2019

14/07/2022

2022

15/06/2022

0

Active

Administration

Supply And Deliver of Pre-Printed Smartcard and Related Consumables

Supply

Company B

Open Tender

RM                        250,000.00

500k and below

83,333.33

3

15/07/2019

14/07/2022

2022

15/06/2022

0

Active

Support Services

Aircraft Noise Monitoring and Air Pollutant Index

Service

Company C

Open Tender

RM                        300,000.00

500k and below

100,000.00

3

01/11/2018

31/10/2021

2021

15/06/2022

#NUM!

Expired

Engineering

Building Maintenance (Architectural, Civil & Structural) of Ancillary Buildings

Maintenance

Company D

Open Tender

RM                   78,000,000.00

More than 10 mil

26,000,000.00

3

1/2/2020

31/1/2023

2023

15/06/2022

7

Active

IT

Calibration Service and Supply & Installation of Spare Parts for  Automate Barcode Reader (BCR)

Supply, Install & Calibrate

Company F

Direct Award

RM                     1,000,000.00

Between 500k to 5mil

333,333.33

3

1/9/2019

31/8/2022

2022

15/06/2022

2

Active

Engineering

Comprehenive Maintenance of 10 units of Elevator at Wing A

Maintenance

Company G

Direct Award

RM                   35,000,000.00

More than 10 mil

8,750,000.00

4

1/1/2020

31/12/2023

2023

15/06/2022

18

Active

Engineering

Comprehensive Maintenance for People Mover

Maintenance

Company H

Direct Award

RM                   30,000,000.00

More than 10 mil

10,000,000.00

3

1/10/2021

30/9/2024

2024

15/06/2022

27

Active

Engineering

Comprehensive Maintenance Contract for Escalators

Maintenance

Company I

Direct Award

RM                   15,000,000.00

More than 10 mil

5,000,000.00

3

1/7/2021

30/6/2024

2024

15/06/2022

24

Active

Security

Comprehensive Maintenance of Screening Machine at Wing A

Maintenance

Company J

Direct Award

RM                   20,000,000.00

More than 10 mil

6,666,666.67

3

1/1/2020

31/12/2022

2022

15/06/2022

6

Active

Engineering

Comprehenive Maintenance of 20 units of Elevator at Wing B

Maintenance

Company K

Direct Award

RM                   15,000,000.00

More than 10 mil

7,500,000.00

2

1/2/2020

31/1/2022

2022

15/06/2022

#NUM!

Expired

Engineering

Comprehensive Maintenance Services People Mover and Dumbwaiters at Wing C

Maintenance

Company L

Direct Award

RM                   10,000,000.00

Between 5mil to 10mil

3,333,333.33

3

1/4/2019

31/3/2022

2022

15/06/2022

#NUM!

Expired

Security

Comprehensive Operation & Maintenance of Screening System at Wing B

Operation & Maintenance

Company M

Direct Award

RM                   25,000,000.00

More than 10 mil

8,333,333.33

3

1/5/2019

30/4/2022

2022

15/06/2022

#NUM!

Expired

Is this correct?

Thank you. 🙂