Dynamic Values of a column

Hi manish_g,

To create the chart that shows the gender-wise breakdown of average amount per collector, I think you could follow these steps:

1. First, you need to create a relationship between the Distributors and Collectors table. To do this, go to the "Modeling" tab and click on "Manage Relationships." Then, create a relationship between the "Name" column in the Distributors table and the "Collector" column in the Collectors table.

2. Next, create a measure to calculate the average amount per collector. You can do this by going to the "Modeling" tab and clicking on "New Measure." Use the following formula:

   Avg Amount per Collector = SUM(Distributors[Amount])/COUNT(Collectors)

3. Now, create a new table by going to the "Modeling" tab and clicking on "New Table." In the formula bar, enter the following formula:

   Gender Breakdown = DISTINCT(Distributors[Gender])

4. Create another measure to calculate the average amount per collector by gender. Use the following formula:

   Avg Amount per Collector by Gender = AVERAGEX( FILTER(Distributors, COUNTROWS(FILTER(Collectors, Collectors[Gender] = Distributors[Gender])) > 0), [Avg Amount per Collector])

   This formula filters the Distributors table to only include those with collectors of the same gender, and then calculates the average amount per collector using the previously created measure.

5. Finally, create a bar chart by dragging the "Gender" column from the Gender Breakdown table to the X-axis and the "Avg Amount per Collector by Gender" measure to the Y-axis.

That should give you a chart that shows the gender-wise breakdown of average amount per collector.

Let me know if it works or if you need any additional help. 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly