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]
PowerDAX Thank you!!!! That worked!!! could you please though further elaborate on how to do the previous and current month?
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...