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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!