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]
Hey mork - basically, you need to use ALLEXCEPT and specify the Resource column:
Work Total:=SUM([Work])
Resource %:=DIVIDE( [Work Total], CALCULATE( [Work Total], ALLEXCEPT( Table1, Table1[Resource])), 0)
For showing only the current month and previous month, the easiest way would be to add a calculated column (TRUE/FALSE - typically on the date table) that would flag the rows if the dates were in the current/previous month. Add that calculated column as a filter and flag as TRUE.
PowerDAX
PowerDAX Thank you!!!! That worked!!! could you please though further elaborate on how to do the previous and current month?
PowerDAX Also your solution isn't 100% right. It creates the a correct percentage when I have the whole timeline selected on the timeline slicer. If I choose a single month the percentages don't add up to 100% per resource.
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 Adding the [month] column to the allexcept function nfortunately doesn't work. When I filter for a month the percentages for each resource still don't add up to 100%.
For example I have a timeline slicer and when the whole range of months is selected each resources percentage adds up to 100%. But when I select a specific month the percentage gets "sliced" too. I want the precentage to be recalculated for the selected month each time and each time to add up to 100% for each resource.
I'll now try the previous and current month solution you provided and let you know how it worked out. Just one thing though. I want the current and previous month separate and not together so I guess I'll just create two calculated columns. Also the format of my month column is mmm/yyyy
Thanks for your help so far! I really appreciate it!
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