Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi, looking for some help
trying to work out a uptime availability for a cloud service, i have working in excel but wish to move it over to powerbi to do all the calculations
for the month, if there are no incidents then the availability should be 100%
each month has a difference amount of minutes due to difference in days which i have a table
if there is an issue calculate per month
- the number of total minutes for that month-the total downtime minutes for that month
- then divide the above figure by the
total minutes for that month
Service Availability =
DIVIDE(SUM('Service Reliability (days in month)'[HoursinMonth])-SUM('Issue'[Downtime]),(SUM('Service Reliability (days in month)'[HoursinMonth])))
this will give me a % availability but if there are is no downtime for that month, then the below matrix gives me blanks rather than 100%. i would like to show 100% in the empty slots below
ive tried a few different isempty, isblank, using VAR, using + 0 at end of measure
all with no luck
im sure im doing somehting wrong. just dont know what
thanks
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Try this
=IF(ISBLANK(SUM('Issue'[Downtime])),1,DIVIDE(SUM('Service Reliability (days in month)'[HoursinMonth])-SUM('Issue'[Downtime]),(SUM('Service Reliability (days in month)'[HoursinMonth]))))
@Ashish_Mathur
thanks for the help. i still get the same output.
i forgot to add the "Group" column in my example above which defines the service/product
would it be caused that i dont have a sum of that product group for that month? as shown in the sum of each month by "Group" matrix table. essentially the missing gaps means there were no downtime minutes for that month
should i be changing how i sum up this first?
Hi,
I am not sure of how much i can help but share the link from where i can download your PBI file. In that file, please clearly show the result you are expecting to see.
here is the link for the file
https://drive.google.com/open?id=1E-OOUOH2GQhiswg6OBfyX3BsTTFsNEwl
the expected output which i have marked in red are all the blanks in the matrix reports that need a value
so 0.00 would be when i am calculating per month the sum of downtime. if there is no downtime, then i should see 0.00
when i work out the uptime % in the bottom table. i need the calculations to say 100% if there has been no affected downtime for that product for that month,
Hi,
You may download my PBI file from here.
Hope this helps.
thanks for this example! I have looked into this but cannot explain the following:
When enabling Subtotals, the numbers seem off:
Isn't the calculation of the total the average across the row? If yes, then how can the actual total be lower than any of the values in the columns?
Is there a misunderstanding on my part?
Hi,
For that example (as old as 2 years), there may not have been a need to show subtotals. that being said, the result in the Total column does seem wrong. Start a new thread with your data, describe your question and show the expected result.
Hi @Ashish_Mathur ,
no problem just wanted to find out if I am misunderstanding something here or if it's really wrong.
I will create a new thread with my data 🙂 thanks!
@Ashish_Mathur
thankyou. im trying to get an understand of the unpivot other columns step you applied. my real spreadsheet has many more columns. im trying to understand what i should be pivoting or not?
thanks
Hi,
Unpivot all columns other than Start time and End time columns.
@Ashish_Mathur
thanks. i have the first 2 tables working great now
im doing something wrong when it comes to calculating the % table. the numbers do not seem to add up
cant seem to find what i have done
Hi,
I cannot tell what mistake you are committing. Compare your file to the solution file i uploaded.
@Ashish_Mathur
all good. the relationship between the tables wasnt correct in my file. i had fixed that and i have validated the data
thank you for the help
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |