Dynamic percentage by date and category

[MauroBote]

Hi friends.

I have the following table (blue columns):

And I need to add 2 new columns (orange ones):

1) DIVIDER: wich is the sum of WEIGHT grouped by PROJECT, and it must be affected by PLAN DATE used as visual filter

2) VALUE: it is WEIGHT divided by DIVIDER (WEIGHT/DIVIDER)

What I mean with PLAN DATE used as visual filter:

Per example if I choose year 2020 in the visual filter, the table must be show like this:

Or If I select year 2019 in the visual filter:

Thanks in advance, I'm looking foward for your help.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[dustinn]

No need to have a separate column for divider.

[MauroBote]

Thanks a lot Dustinn!

It work perfectly in case that the filter is just year, but I need to set up the visual filter as "between" (period of time and not just years) and in that case it doesn't work.

Do you know how to solve it?

between

[dustinn]

@MauroBote Use 'Table'[Plan Date] since you are using the Plan Date filter itself. 

[MauroBote]

Like this:

Measure = DIVIDE(SUM('Table'[WEIGHT]);CALCULATE(SUM('Table'[WEIGHT]);ALLEXCEPT('Table';'Table'[PROJECT];'Table'[PLAN DATE]));0)

It doesn't work either, because for the example the measure of tasks at4 and at5 should be 60% and 40% respectively.

[dustinn · Accepted solution]

I see, it'll only work if you change the Plan Date on the table to Date Hierarchy so try this one and lets see if it works:

I'll advise to make a Date table then you can also select the fields you want to use as filter there 

1. Make a Date table. At Modeling tab, click on New Table and insert the DAX - 
   Date = CALENDAR(MIN('Table'[Plan Date]),MAX('Table'[Plan Date]))

   (Optional, create a new column for year, month, etc.)

   
2. At Model on the left side, create a relationship between the 2 tables, Table[Plan Date] = Date[Date]
3. Edit your original measure to: 
   Measure = DIVIDE(SUM('Table'[Weight]),CALCULATE(SUM('Table'[Weight]),ALLEXCEPT('Table','Table'[Project],'Date'[Date])))
   

[MauroBote]

Perfect!!! You are a genius!!!

Thankssss!!!