Is possible to filter by subtotals?

[AndresSalomon]

Hi Community, my first post here. I've been working with DAX for a few months but now I'm facing a situation that I cannot solve. My situation is a bit complicated to write but I will do my best summarizing it, so you can easily understand. 

I have a report which has a table like this from where I get the data:

Name________Country____________WBS_________Month________Allocation

Name1_______USA________________WBS1________Jan___________1        

Name1_______USA________________WBS2________Jan___________1

Name2_______China______________WBS3________Jan___________1

Name2_______China______________WBS4________Feb___________1

Name2_______China______________WBS5________Feb___________1

The objective here is identified the people that is "Over-allocated" to a WBS (considering 1 as the maximun allocation)

So, I created a simple measure: Allocation Measure = SUM ([Allocation]). Then I created a Pivot Table like below (I'm not writing all the months for practical reasons, but imagine the whole picture of the whole year). The measure goes in the "Values" field.

Name________WBS________Jan_______Feb_____Mar...............
Name1______WBS1________1_________0________
______________WBS2________1_________0________ 
Name1 Total_______________2_________0________ (This is the "Subtotal" feature from Pivot Tables)
Name2______WBS3________1_________0_________

______________WBS4________0_________1_________
______________WBS5________0_________1_________
Name2 Total_______________1_________2_________

So, the folk from USA is overallocated in January and the other one from China is overallocated in February (the thing here is to imagine that there are thousands of rows and all the months)

What I'm trying to do is to filter the rows (if is possible, dynamically, like a slicer) by the Subtotal feature in the Pivot Table, because, as you can imagine, not all the people is overallocated, and I want to leave only whose Subtotal are bigger than a value choseen by the user (for example bigger or equal than 2, or bigger than 1.5).

I was trying, first, to identified the row of the Subtotal, based on the post from Rob Collie here (CREDITS FOR HIM):
https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

And combining that with the Parameter Table Pattern from the site of Marco Russo and Alberto Ferrari (CREDITS FOR THEM):
http://www.daxpatterns.com/parameter-table/

So the idea was identified the current row as a Subtotal row, and then use a Parameter table (let's call it 'Filters', with the column [Filters] to give the user options to filter by different values, but clearly my knowledge does not go so far...

I tried something like this with some variants, but it doesn't work. It just keep me filtering by the values on the common rows, and not based on the Subtotal row.

Allocation Measure Test:=IF(COUNTROWS(VALUES([Name]))=1,
[Allocation Measure],
IF(HASONEVALUE('Filters'[Filters]),
SUMX(FILTER(Data, [Allocation]>=VALUES('Filters'[Filters]))),
[Allocation Measure])
)
)

I think I'm mixing everything up, so frustrating... Any advice or workaround (or solution, GOD BLESS YOU) here would be mostly appreciated. Please let me know if you need more details or if I didn't explain myself well or any mistake you see.

Really thanks in advance.

Regards,

Andy.-

[TomMartens · Accepted solution]

Hey,

I would create a calculated column like so

OverAllocation = 
IF(
  CALCULATE(
    SUM('table'[Allocation])
    ,ALLEXCEPT('table','table'[Name],'table'[Month])
  ) > 1
,"True"
,"False"
)

It's necessary to use CALCULATE() to create a calculated column, because SUM() eventually has to add more than one row, but not all of the rows.

ALLEXCEPT() allows to expand the Filter Context added to the current row by using CALCULATE(), precisely - the implicit Row Context is transformet into a Filter Context.

ALLEXCEPT() removes the filter of all columns, except from the Name and the Month column.

Please be aware, that you may have to consider the year as well, if there is a date column somewhere in your table.

Now you can use this column to slice your data using all the possibilities of Power BI

Hope this helps

Regards

Tom

[AndresSalomon]

Hey Tom, thanks for replying! Much appreciated. 

I tried this, and is working in a weird way... It doesn't filter the Overallocated, it only leave Blank those that have a value equal to zero, but keep all the rows. Like:

Before filtering by "True":

Name________WBS_________Jan_______Feb_______Mar
Name1______WBS1_________0__________0________1

After filtering by "True":

Name________WBS_________Jan_______Feb_______Mar
Name1______WBS1_________   __________   ________1

And if I filter by "False" it leaves an empty table.

Do you understand what I mean?

Also, for example, where am I telling it that I want to keep those people that have (in their Subtotal row) more than a specific value?

Maybe I'm not fully understanding the Calculated Column you provided. Sorry about this, still learning DAX.

Thanks again! 

Regards, 

Andy.-

[TomMartens]

Hey,

you are totally right, if you say you can't understand the workings of the formula, this is due to the following - the formula is wrong, I missed an essential part "IF( ... > 1 ,

I just corrected this in my first post - sorry for that

Regards

Tom

[AndresSalomon]

Well Tom, this is actually working, at least as a first view/check. I marked it as a solution, and of course I have no words to thank you!!! 

Now, I don't want to sound greedy, but do you think that there is a possibility to make that ">1" a variable that the user can enter? Because it may exist the possibility that it is allowed that a person has a 1.1 or 1.2 allocation, depends on the user. 

Just curious about it, but if you think this deserves another post, just don't worry, you already made my day, perhaps too much days. 

Really thank you again! 

Regards,

Andy.-