Forum Discussion

Jacob1832's avatar
Jacob1832
Frequent Visitor
4 years ago
Solved

Average that changes with filter

Ive got a data set with like this
```

LeadSource    Dealer    OurLead    SysSize

A                     Yes         True            2.5

A                     No          False          3.8

B                     No         True            5.6

B                     Yes         False          8.0

```-
I want to create a table that tells me the average of the SysSize column per LeadSource, with a filter based on the Dealer and OurLead column that changes the rows included. So with no filter, it would give an average of 3.15 for A, and 6.8 for B.

 

With the filter of Yes for Dealer, it would be 2.5 for A and 8.0 for B.

With the filter of True for OurLead, it would be 2.5 for A and 5.6 for B. 

With the filter Yes and True, A would be 2.5 and B wouldn't be present
And so on. 

Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Hi Jacob1832 ,

    Please try creating this calculated table in DAX

    Avg Lead Source =
    SUMMARIZE(LeadSource,
    LeadSource[LeadSource],
    LeadSource[Dealer],
    LeadSource[OurLead],
    LeadSource[SysSize],
    "Avg Sys Size", AVERAGEX(LeadSource, LeadSource[SysSize])
    )

     

    Please ensure summarization is set to "Average" for Avg Sys Size


    Based on this, here are the results : 

    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos!

     

    Kind regards,

    Rohit

     

     

     




3 Replies

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi Jacob1832 ,

    Please try creating this calculated table in DAX

    Avg Lead Source =
    SUMMARIZE(LeadSource,
    LeadSource[LeadSource],
    LeadSource[Dealer],
    LeadSource[OurLead],
    LeadSource[SysSize],
    "Avg Sys Size", AVERAGEX(LeadSource, LeadSource[SysSize])
    )

     

    Please ensure summarization is set to "Average" for Avg Sys Size


    Based on this, here are the results : 

    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos!

     

    Kind regards,

    Rohit

     

     

     




    • Jacob1832's avatar
      Jacob1832
      Frequent Visitor
      4 years ago

      Thanks so much! Is there a way to create multiple tables that would all filter off the same filters? OurLead and Dealer? Is there anything special to do or if I create another table from the same or a different data set, will that filter work on it?

       

      • rohit_singh's avatar
        rohit_singh
        Solution Sage
        4 years ago

        Hi Jacob1832 ,

        No problem. Happy to help. You can create multiple tables from the same table without issue. The only thing to keep in mind if you want to filter both(or multiple) tables using the same filters is that you will need to create additional dimension tables for each filter, and then create a 1:M relationship of each new dimesnion table with the data table. 


        dim_dealer table has two values yes and no
        dim_ourlead has two values true and false

        Once you have created the above relationships, use the columns in the dimesnion tables as your filters and both tables will be filtered simultaneously

        Kind regards,

        Rohit

        Appreciate your kudos!😊