How to add a formula to a pie chart not found in my SharePoint List

[lpmdvip]

I have a SharePoint list with a downtime percentage column. In the example, the category represents downtime for those particular categories. Those two downtime categories equal 0.25136 percent. I would like to add an Uptime value that would equal 99.74864 so that the pie would equal 100% (100 - 0.25136) with those 3 parts. There will be more future categories, but would still like to subtract all of those category values from 100 to get the Uptime value. 

  Any suggestions would be appreciated!

[v-eqin-msft]

Hi @lpmdvip ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @lpmdvip ,

Please take a look at my data sample and use the following formula:

VPN	08:00:00	08:00:21
SQL Databases	15:32:58	15:33:37

Down Time =
(
    HOUR ( MAX ( 'Table'[Down end time] ) ) * 3600
        + MINUTE ( MAX ( 'Table'[Down end time] ) ) * 60
        + SECOND ( MAX ( 'Table'[Down end time] ) )
        - HOUR ( MAX ( 'Table'[Down start time] ) ) * 3600
        - MINUTE ( MAX ( 'Table'[Down start time] ) ) * 60
        - SECOND ( MAX ( 'Table'[Down start time] ) )
) / ( 24 * 3600 )

Up Time =
1 - [Down Time]

My visualization is shown below:

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[DataInsights · Accepted solution]

@lpmdvip,

Try this approach:

1. In Power Query, add a custom column "Type" with value "Down". Use column headings as shown below. In this example, the table name is "NetworkData".

2. Create a calculated table with the following DAX:

NetworkCalc = 
VAR vTotalDowntime =
    SUM ( NetworkData[% Time] )
VAR vUptime = 100 - vTotalDowntime
RETURN
    UNION ( NetworkData, { ( "Uptime", vUptime, "Up" ) } )

This is how the calculated table will look:

3. Create a visual using the table "NetworkCalc". I used a column chart since the data doesn't display well in a pie chart.

[lpmdvip]

@DataInsights 

Thank you for the information, but I am receiving this notification when I build the new table. My original table UpTime Report has many columns.

[DataInsights]

@lpmdvip,

Try this:

Uptime Report Calc = 
VAR vTotalDowntime =
    SUM ( 'Uptime Report'[% Time] )
VAR vUptime = 100 - vTotalDowntime
VAR vNetworkData =
    ADDCOLUMNS (
        SUMMARIZE ( 'Uptime Report', 'Uptime Report'[Category] ),
        "% Time", CALCULATE ( SUM ( 'Uptime Report'[% Time] ) )
    )
RETURN
    UNION ( vNetworkData, { ( "Uptime", vUptime ) } )