Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mcc
Frequent Visitor

Power BI: Total % not working when filtered

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.

 

 

 

9 REPLIES 9
Anonymous
Not applicable

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.

mcc
Frequent Visitor

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.  

Anonymous
Not applicable

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.

mcc
Frequent Visitor

Here is the original.  I should have sent that vs. what we were doing to play around.  

 

mcc_0-1624392259036.png

 

And I made 2 measures: 

  • DT_Hours which is =sum(Incidents[Downtime Hours])
  • TotalHours which is =sum(Dates[Hours])

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.

 

mcc_2-1624392592385.png

 

mcc
Frequent Visitor

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:  

% = (SUM(Incidents[Downtime Hours]) / [TotalHours])*100
 
It works beautifully now.  🙂

 

mcc
Frequent Visitor

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  

mcc
Frequent Visitor

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?  

amitchandak
Super User
Super User

@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.  

 

mcc_0-1624374429820.png

 

mcc_1-1624374531531.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors