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?
