Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
power bi in online application. Table with incidents by location, date, hours down and % down for per day for a 1 year period.
1 want the % downtime by location.
when I have all locations for 1 year, it' calculates correctly...8760 hours. Downtime is 777 hours which equals 8.86% down. If I filter by June 2020, it calculate correctly, 720 hours, downtime is 73 which equals 10.13%. But when I filter on location, it bases the % only on the dates with an incident. In June there were 4 dates with incidents in Texas with a total downtime of 6.5 hours. So instead of giving me 720 hours and 0.9% down, it gives me 96 hours and 6.7% down.
I added a measure totalhours=sum(total hours). And total downtime= sum(total downtime). Then a new measure to divide both. I have ticked show times without data. I have also played with the interactions. Nothing is working when filtered by location. Any ideas?
thank you.
That's because your formula responds to all slicers and calculates the figure within data that's visible in the current context. If you want to calculate the downtime relative to all the locations in the underlying base table (when only a subset of locations is selected in a slicer), you have to make sure that the denominator removes any filters from the Location column. The directive to use is ALL or REMOVEFILTERS.
However, if your model consists of just one table, then... be prepared that the calculation will sometimes be wrong, even if the formula is totally correct. You should never create models with just one table. If you want to know why, watch this: Auto-exist on clusters or numbers – Unplugged #22 - SQLBI
You've been warned.
I have multiple tables, including a data table (that is using a query to create the date table based on the incident table), a location table, etc., etc.
Sorry to say that but your model is quite confusing. You've got at least 4 tables that have something to do with downtime hours. In Dates there's a column DTHours. There's Downtime_Hours and DT Hours By Date (which is not only one-to-one with Dates but also has some names of columns that are the same in Dates; one-to-one relationships should be avoided since the tables are effectively one and the same table). And there's also a column in the fact table. Very confusing. I would advise against such a design. If this is what your users see, then I wonder how they'll know which column to use for what. On top of that, I would also strongly advise against using bi-directional filtering. There are just too many reasons to put them down in this post. Bi-directional filtering should be used to handle many-to-many scenarios where bridge tables exist. Other uses are very risky and, of course, slow down the whole thing.
Here is the original. I should have sent that vs. what we were doing to play around.
And I made 2 measures:
To get the percentage, I added another measure: DIVIDE([DT_Hours], [TotalHours],0,)
This works on all filters except location and region. I am sure I am missing a paramenter, but cannot think how to set it up properly.
This issue is closed. It was a simple solution. I had the mapping of the Date and Incident table allowing BOTH on cross filter direction. I turned that to single. Additionally, instead of using the formula I mentioned before, we changed the fomula to the below:
I get that. Our sql guy added those tables to try variations without messing up the original tables.
The original tables (which are the ones used on the visuals) are below. Only 2 of these tables have dates: Incidents and Dates. The incident table has all of the parameters.
- Incidents
- Dates
- Location
- Region
- Asset
- Cause
I just want the Date that is selected to use those hours. Example: June has 720 hours. So, if I pick the month of June, I want it to calculate Downtime hours / 720 hours. However, it is only calculating the days in June that the location had an incident. What is the solution for this?
@mcc , Are you using a formula like
divide(sum(Table[hours down]), calculate(sum(Table[hours down]), allselected(Table)))
can you share your formula
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
How do I upload my file on this? I understand it has something to do with the location, but not sure how to fix it. I don't want a date table for every location. And this is SOOOO simple in excel. Obviously I am missing something.
When I write divide(sum(table...Dax will not accept it.
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |