cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Dynamic filtering+calculation

Hello,
I am trying to migrate some functionalities from a Python code to PowerBI. So far the idea was to keep data cleaning in Python and migrate the visualisation and allow dynamic filtering and data processing via slicer.

We have weekly forms collecting how our team is spending how much time in which project. The data are on weekly/per-employee granularity and aggregate as total per month in Python. Long story short, at the end we get a dataframe with "ProjectName" vs. "Month", "FTE/Month" where:

  • "ProjectName is a string;
  • "Month" is a string: 'Jan', 'Feb'...'Dec';
  • FTE/Month is a float.

I import the data in PowerBi as excel and Pivot the table obtaining the following (DummyData)

Capture.PNG

 

I also create an extra Table containing order of the month in the year, long name for the months, and number of week per month. I then have a mapping from the <Month> of the first Table to <Short_month> and enable cross filter direction in both direction. This extra table is required mainly to go from FTE to working day (via the <Week per month> column) but also allows me via the <Order> to have the slicer NOT in alfabetical order but in cronological order.
Capture.PNG

Until here all ok. The actual functionality we want to implement is the following: 
we want to allow the user to chose on a slicer the month(s), e.g. May, or May+June... and see the % of the time that people has spent in which project in that/those month(s), e.g. via TreeMap. This requires to change the base against which the percentage is calculated, e.g.

  • if in May ProjectA is 0.5 FTE and total May is 5FTE --> ProjectA = 10%;
  • if in May+June ProjectA is 0.5+1 FTE and total May+June is 10FTE --> ProjectA = 15%

I tried several DAX combinations of CALCULATE or SUMX+FILTER assuming that once I choose a value in the slice the table gets filtered and I can just pick the value per projectName / total of Project but it doesn't seem to work.

Any suggestion? I would attache the file but apparently new user cannot do it...and from the work PC I am not allowed to share anything.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Dynamic filtering+calculation

That helped a lot. I was able to replicate your scenario. I named the table with the projects just `Projects`.

 

This is the measure that does what you need:

Value% = 
DIVIDE(
    SUM(Projects[Value]),
    SUMX(
        ALLSELECTED(Projects),
        Projects[Value])
)
 
Please let me know and mark as solution if I was able to help you.

View solution in original post

4 REPLIES 4
Highlighted
Microsoft
Microsoft

Re: Dynamic filtering+calculation

I am afraid without more info / dataset it is hard to understand what you are trying to do here.

Highlighted
Regular Visitor

Re: Dynamic filtering+calculation

I understand, it is quite bad not be able to upload directly file. Given that PowerBi is "designed for Business" and most of the business do not allow to share things from the work pc, it is very limiting. I try to provide some extra information...

I have 2 Tables:

  • Table1 contains X rows and 3 columns: ProjectName, Month, Value;
  • Table2 is connected via Month to the first Table, and contains some value I want to be able to map to the month of each row of the first Table.

E.g. For semplicity let's assume Table 1 is this

ProjectNameMonthValue
Project AJul0.1
Project AAug0.5
Project ASep0.2
Project BJul0.3
Project BAug0
Project BSep1

 

Table 2 is the one reported in the pic in my main post.

I have a Slicer with Value <Long_month> from Table2 (which is linked to month in Table1, e.g. "Aug" <-> "August")

The functionality I am looking for is the following;

  • if the user choose "August" in the slicer the visualisation should filter the data for "Aug", and calculate (measure) the % of Value for each filtered row (Month=Aug) with respect to the total (sum) of value for the rows that contain "Aug" as month. The visualisation should then be ProjectA = % of Value[ProjectA] in Aug. 

    Slicer = August; Visualisation: ProjectA = 0.5/(0.5+0) = 1 (100%), ProjectB = 0/(0.5+0) = 0 (0%)
  • if the user choose "August+September" in the slicer the visualisation should filter the data for "Aug"+"Sep", and calculate (measure) the % of Value for each row with respect to the total (sum) of value for the rows that contain "Aug" AND "Sept" as month;
    Slicer = August+September; Visualisation: ProjectA = (0.5+0.2)/(0.5+0+0.2+1) = 0.41 (41%), ProjectB = (0+1)/(0.5+0+0.2+1) = 0.59 (59%)

I hope this helps.

Highlighted
Microsoft
Microsoft

Re: Dynamic filtering+calculation

That helped a lot. I was able to replicate your scenario. I named the table with the projects just `Projects`.

 

This is the measure that does what you need:

Value% = 
DIVIDE(
    SUM(Projects[Value]),
    SUMX(
        ALLSELECTED(Projects),
        Projects[Value])
)
 
Please let me know and mark as solution if I was able to help you.

View solution in original post

Highlighted
Regular Visitor

Re: Dynamic filtering+calculation

It solves 90% of what I wanted to do. The challenge is that in the visualisation it does not filter only for the Month (through the slicer) but also if I, e.g., choose to visualise the top 10 projects. If I do this the visualised % on the Treemap re-calculate on Top-10-project total.

I think however it is a good starting point...also, if I have a Table nearby the % are correct.

Thanks a lot

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors