Forum Discussion
Help with DAX
Sorry - I should have been a little more clear.
Whatever you want to drive the calculation of the full 100% needs to be in the ALLEXCEPT exclusion.....i.e.:
DIVIDE( [Work Total], CALCULATE( [Work Total], ALLEXCEPT( Table1, Table1[Resource], Table1[Month])), 0)
If you wanted it to be treated differently based on what/if things were filtered, you could use an IF or SWITCH if you had multiple conditions....i.e. IF( ISFILTERED( Table[Column]) or IF( ISCROSSFILTERED( Table[Column]
For example, if your month column is the first of the month, you would add a calculated column with something like this:
=IF([Month]=DATE(YEAR(NOW()), MONTH(NOW()), 1) || [Month] = EDATE(DATE(YEAR(NOW()), MONTH(NOW()), 1),-1) , TRUE(), FALSE())
This says if the month date (i.e. 4/1/2016) = the first of the current month or the first of last month, flag it as true, otherwise false. You would then add that column as a filter at whatever level you required it (i.e. visual - chart only, page - full page - or report - all pages).
PowerDAX I managed to create the two flag colums and now I can filter the data! The only problem tha still remains is the percentage when I filter for a specific month. Any clue on why it doesn't show up correctly?
Isn't there anyone else that can help me out? Just to recap, I have this formula
DIVIDE( [Work Total], CALCULATE( [Work Total], ALLEXCEPT( Table1, Table1[Resource], Table1[Month])), 0)
That gives me the bellow matrix visual:
Country 1 Country 2 country 3
Resource Dept.1 Dept. 2 Dept.3 Dept. 1 Dept.2 Dept.3 Dept.1 Dept.2 Dept.3
John 20 % 20% 20% 40% (=100%)
Jim 40% 20% 30% 10% (=100%)
When I try to filter that for a month though I get something like bellow:
Country 1 Country 2 country 3
Resource Dept.1 Dept. 2 Dept.3 Dept. 1 Dept.2 Dept.3 Dept.1 Dept.2 Dept.3
John 10 % 2% 5% 15% (=32%)
Jim 20% 10% 10% 5% (=45%)
What I want is to have percentages that add up to 100% for each resource no matter how I filter the timeline. That way I will be able to see the distribution of the resources in a specific month, in specific quarter or even yearly.
My original dataset looks like bellow:
Month Resource ResourceDept Work Department Country Work
1/16 John ResDept.1 Dept. 1 Country 1 40
1/16 John ResDept.1 Dept. 2 Country 1 40
1/16 john ResDept.1 40
1/16 Jim ResDept.2 Dept. 2 country 2 40
2/16 Jim ResDept.2 Dept. 1 country 1 40
2/16 John ResDept.1 Dept. 2 country 2 40
Nevermind, I figured it out. I was filtering from the wrong table...