Forum Discussion
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)
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.
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.
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.
4 Replies
I am afraid without more info / dataset it is hard to understand what you are trying to do here.
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
ProjectName Month Value Project A Jul 0.1 Project A Aug 0.5 Project A Sep 0.2 Project B Jul 0.3 Project B Aug 0 Project B Sep 1 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.
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.
