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]
I tried the following formula in a measure which kinda worked.
Percentage= SUM(Table[Work])/ CALCULATE(SUM(Table[Work]);ALL(Table))
I can see the percentage per country and department and resource though. But thats not what I want I want each resource's work to add up to 100%. 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 20 % 20% 20% 40% (=100%)
Jim 40% 20% 30% 10% (=100%)
Currently what I managed to do is this:
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 5% 5% 20% 20% (=50%)
Jim 10% 20% 10% 10% (=50%)
And I also haven't figured a way to have only the previous month or the current month shown. I could use a timeline slicer but at any given time I only want to see the current and previous month and not having to select the months myself.
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
mork and If you want to exclude Blank Work Departments
Work Total = CALCULATE (SUM[WORK]), Table1[Work Department]<>"")
Sean I don't want to exclude the blanks. What I want though is to have them show at the same table but not under a blank column but under the resource department column.
For example something like this.
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 ResDept.1 ResDept.2
John 20 % 20% 20% 30% 10%
Jim 40% 20% 20% 10% 10%
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]
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).
