Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MauroBote
Helper I
Helper I

Dynamic percentage by date and category

Hi friends.

 

I have the following table (blue columns):

Imagen2.jpg

 

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:

Imagen3.jpg

Or If I select year 2019 in the visual filter:

Imagen4.jpg

 

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

1 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.)
    1.1.png
  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])))
    1.2.png

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dustinn
Resolver I
Resolver I

No need to have a separate column for divider.1.png

 

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?

 

betweenbetween

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

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.

 

Imagen6.jpg

 

 

 

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.)
    1.1.png
  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])))
    1.2.png

Perfect!!! You are a genius!!!

Thankssss!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.