Forum Discussion

mork's avatar
mork
Helper V
10 years ago
Solved

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 Dep...
Need Help
  • PowerDAX's avatar
    PowerDAX
    10 years ago

    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's avatar
mork
Helper V
10 years ago

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's avatar
PowerDAX
Resolver III
10 years ago

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's avatar
    mork
    Helper V
    10 years ago

    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!