Dynamic Values of a column

[manish_g]

Hello Community, 

  I am facing difficulity with the following scenario

There is a table distributors with columns Name, Gender and Amount

Another table collector, which contains a measure with count of collectors(no of collectors) 

Here I need a chart which shows the gender wise breakdown of avg amount per collector 

(A bar chart with X- axis -- Gender and Y-axis Avg amount per collector) 

I would be thankful for your suggestions. 

[MohammadLoran25]

Hi

Please provide a sample PBI file which contains your data and data model.

[ichavarria]

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

[manish_g]

In point no 4 you have used this Collectors[Gender], but there is no Gender in Collectors. 

Collectors aren't connected to distributors table. Clooector only contains Name, Id and address.