DAX ALL not working outside filters

[fluke4]

Hi,

Would someone please help me understand what is going on? I have a table like so (called Table1) that tracks my time spent in different categories:

The duration column is in hours. I have a Power BI report that lets me click on categories in a pie chart and see the amount of time I spent on each task in that category. Also, I have a slicer that lets me narrow things down based on start time. Simple.

I click on a category in my pie chart, I choose a start date range in my slicer, and I see a table that shows the amount of time I spent just for tasks in the selected category - like so:

The Overall Percentage measure I've created isn't working. It SHOULD show the percentage of time I spent on that task (out of all the time I spent on all tasks and all categories). What it CURRENTLY is showing is the total number of hours (or what will be the denominator of my percentage equation). Here is the DAX for that field:

Overall Percentage = CALCULATE(SUM(Table1[Duration]), ALLEXCEPT(Table1, Table1[Start Time]))

I was expecting the Overall Percentage to show me 42, not 21.45. 42 is the number of total hours spent on all tasks. Why is the ALLEXCEPT function in my DAX not including rows outside the category filter applied when I click on my pie chart?

[Anonymous]

HI @fluke4,

You can try to use below formula if it suitable for your requirement.

Overall Percentage =
VAR current_category =
    LASTNONBLANK ( Table[Category], [Category] )
VAR current_task =
    LASTNONBLANK ( Table[Task], [Task] )
RETURN
    SUMX (
        FILTER (
            SUMMARIZE ( Table1, [Category], [Task], "Sum", SUM ( Table1[Duration] ) ),
            [Category] = current_category
                && [Task] = current_task
        ),
        [Sum]
    )

If above not help, please share some sample data to test

Regards,

Xiaoxin Sheng

[fluke4 · Accepted solution]

Thanks for your help. Unfortunately, it didn't work, but I figured out what I needed to do. I had to add another column to my dataset. I called it "WorkWeek" which was basically the week number and the year. So for today, the "WorkWeek" would be 50-2017. 

I then used the following equation:

Overall Percentage = 
VAR
	wkYr = MIN(Table1[WorkWeek])
RETURN	
	SUM(Table1[Duration]) / CALCULATE(SUMX(Table1, Table1[Duration]), ALL(Table1), Table1[WorkWeek] = wkYr)

I saved the WorkWeek, then I calculate on the full table where the work week is the same as my saved variable.