cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DAX SUM and ignore page level filters

Hi Guys,

 

Im new to Power Bi and its functionality but starting to get into writing DAX to customise my dashboards etc.

 

Ive come across a challenging one:

 

Im wanting to display a stacked bar chart with the following settings:

Project name on the Axis

Sum of Project Hours in the bar

A filter bar at the top for Task Name

 

So when the task name is changed at the top each bar for each Project Name should have 2 segments (Sum of Project hours that is that task name and the Sum of the task names that are not equal to the Task name) - The latter part is what im struggling at getting. Ive tried multiple methods ive seen online but none of which seems to work.

 

I just need to work out for each Project that is down the bar charts what the percentage is for the selected task out of the total project hours.

 

Can anyone point me in the right direction?

Thanks in advance

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

try this measure

 

All other hours =
 if(
HASONEFILTER(Table1[Task]), 
Calculate(Sum(Table1[hrs]), Filter(ALL(Table1[Task]),  Table1[Task] <> MAX(Table1[Task))), 
sum(Table1[hrs])
)

drop this new measure on values as well. so you will see two bars for each project and if no task selected in slicer, both bar will have the same value, if any task is selected in slicer, you will see one bar of total hours of selected task and other bar (this new m easure) will be sum of hours for all other tasks.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

try this measure

 

All other hours =
 if(
HASONEFILTER(Table1[Task]), 
Calculate(Sum(Table1[hrs]), Filter(ALL(Table1[Task]),  Table1[Task] <> MAX(Table1[Task))), 
sum(Table1[hrs])
)

drop this new measure on values as well. so you will see two bars for each project and if no task selected in slicer, both bar will have the same value, if any task is selected in slicer, you will see one bar of total hours of selected task and other bar (this new m easure) will be sum of hours for all other tasks.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Anonymous
Not applicable

Hi @parry2k

 

Thanks again for replying with the measure.

However, It doesnt appear to give me the result I was expecting.

 

Ive got another tab where I can select 1 project and then it shows a pie chart with each task on as a percentage.

So on the tab where im using your measure Id expect the Electrical Modelling to be 30.94% like it is on the single project tab wheras its showing as 7%

 

Capture.PNGCapture1.PNG

 

Many Thanks

Ant

Anonymous
Not applicable

Wow thats amazing!

Thanks for the quick reply

 

Been trying to figure this out for hours!!

Thanks again

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!