cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Super User VII
Super User VII

Re: DAX SUM and ignore page level filters

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
Highlighted
Super User VII
Super User VII

Re: DAX SUM and ignore page level filters

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

Highlighted
Anonymous
Not applicable

Re: DAX SUM and ignore page level filters

Wow thats amazing!

Thanks for the quick reply

 

Been trying to figure this out for hours!!

Thanks again

Highlighted
Anonymous
Not applicable

Re: DAX SUM and ignore page level filters

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

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors