Forum Discussion
Help with DAX
Hello all,
I have a table that shows work per month per resource per department per country. The table looks something 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
When there is a row with blank Department and country is non chargeable work (administrative, travelling etc)
I'm trying to create two tables that will look something like bellow. The first table I want to show data of the previous month, and th second table data of the current month. the layout of both tables will be the same.
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 % % % %
Jim % % % %
Basically I want a table that will show me the distribution of each resource's work in each country and each department in percentage. I can recreate the table with my current data, but how can I turn that data into percentage using DAX?
and also how can I have two seperate tables one for the current and one for the previous month? I guess I can achieve both of these using DAX.
For example I want to be able to see in previous month in what percentage my resources worked in each country and dept. and compare that to the current month.
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]
15 Replies
No one?? I just want the formula for the percentage of the work per country per department!
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
I would do multiple calculations. First, Sum the hours.
Total Hours = SUM(Table[Hours])
Total Hours Last Month = CALCULATE([Total Hours], DATEADD(Date Table[Date Key], -1, Month))
Hours Growth % = DIVIDE([Total Hours], [Total Hours Last Month]) -- be sure to format this as a percentage in the ribbon.
Use your Matrix Rows and Visual Axis to seperate by month.
