Help with DAX

[mork]

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.

[kcantor]

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.

[mork]

@kcantor Sorry but I don't understand what this will do exactly. What should I put at the DATEADD function?

And also if I understand correctly this will show me the previous month. which is good, but how can I get the percentage per resource too and not for all the resources combined?

[mork]

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.

[PowerDAX]

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]

 @PowerDAX Thank you!!!! That worked!!! could you please though further elaborate on how to do the previous and current month?

[PowerDAX]

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).

[mork]

@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? 

[mork]

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

[mork]

Nevermind, I figured it out. I was filtering from the wrong table...

[mork]

@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.

[PowerDAX · Accepted solution]

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]

[mork]

@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!

[Sean]

@mork  and If you want to exclude Blank Work Departments

Work Total = CALCULATE (SUM[WORK]), Table1[Work Department]<>"")

[mork]

@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%

[mork]

No one?? I just want the formula for the percentage of the work per country per department!