Average that changes with filter

[Jacob1832]

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. 

[rohit_singh · Accepted solution]

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]

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]

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!😊