Forum Discussion

PiccinelliSte's avatar
PiccinelliSte
Regular Visitor
6 years ago
Solved

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 da...
Need Help
  • jeroenterheerdt's avatar
    jeroenterheerdt
    6 years ago

    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.
jeroenterheerdt's avatar
jeroenterheerdt
Microsoft Employee
6 years ago

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

  • PiccinelliSte's avatar
    PiccinelliSte
    Regular Visitor
    6 years ago

    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.

    • jeroenterheerdt's avatar
      jeroenterheerdt
      Microsoft Employee
      6 years ago

      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.
      • PiccinelliSte's avatar
        PiccinelliSte
        Regular Visitor
        6 years ago

        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