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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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