Forum Discussion

Ben_Kronk's avatar
Ben_Kronk
Frequent Visitor
6 years ago

Create a filtered average column by combining two measures

I am new to Power BI and I want to create a new column that an average of the total of two other columns.

 

I can create the total using a measure:  TOTAL 1 = DISTINCTCOUNT(Database[Column 1])

 

I can also create the other total: TOTAL 2 = SUM(Database[Column 2])

 

I want to divide the total of TOTAL 2 by TOTAL 1.  But when I do this, I get the average of the entire database.  I want the average of just the table and/or visual that I created using filters.

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    You will need a measure then, like this:

    Measure = DIVIDE(SUM(Database[Column 2]), DISTINCTCOUNT(Database[Column 1]),0)
    • Ben_Kronk's avatar
      Ben_Kronk
      Frequent Visitor
      6 years ago

      Okay got it.  My final step is take the average measure (.59) and make that number repeat in its own column.

       

      Ex.

      .88   .59

      .47   .59

      .24   .59

      Etc.

       

      My goal is to create a dual axis chart with bars of different heights for each value, and a consistent line across the entire chart - I can create this chart on my own.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    If interaction is not off and you are not using all in divide it should filter by slicer.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Ben_Kronk,

    Unfortunately, current power bi does not support creating a dynamic calculated column/table based on filter/slicer.
    When you use measure in the calculated column, these row contents will be fixed to 'whole table' and not able to interact with slicer/filter. 

    Regards,

    Xiaoxin Sheng