Averages of totals across two tables and forcing zero value

[SharonHMA]

Hi all,

I am having trouble aggregating totals & averages correctly across two data sets.  Here's an example:

I have two data sets.  Data set one (from an excel sheet) records each sale. Eg:

Client Store Amount Group
Person 1 StoreA $5 Cards
Person 1 StoreB $8 Books
Person 1 StoreA $9 Wires
Person 1 StoreC $11 Stamps
Person 2 StoreB $6 Books
Person 2 StoreD $4 Books
Person 2 StoreE $12 Wires
Person 3 StoreB $8 Books
Person 3 StoreC $2 Cards

I have a second data set (client data) that has other information for each person which contains other information.  I have a relationship between the data sets based on client number.

My problem:

I can calculate correctly within data set 1 the total sales by person by Group from my sales set:

Person 1 

  Cards $5

  Books $8

  Wires $9

  Stamps $11

Person 2

  Books $20

  Wires $12

Person 3

  Books $8

  Cards $2

My problem is how to calculate average sales by group for every person in data set 2, where if someone doesn't have a purchase in a group the assumed purchase amount for that person should be $0, then I'd like to average all of these. 

So end result looks like:

Averages by Group

Cards = (5+0+2)/3

Books = (8+20+8)/3

Wires = (9+12+0)/3

Stampes = (11+0+0)/3

Manually I've done this before by creating a calculation in datatable 1 for total, creating tableview by Group, exporting then putting this into my dataset2 (an excel sheet) manually, forcing a zero for clients without a purchase.  However that seems silly.  

I'd appreciate any thoughts on how to do this within powerbi (as this is just one simple example, I have a few other groups to do as well)

Thanks

[dkay84_PowerBI]

One way to do this is through "group by" functionality in query editor. Group by person then group and use the aggregation of average.

In DAX, you can accomplish this using SUMMARIZE or, if you create a matrix, put group on the rows or columns and put person ID on the other, and put your Amount as the value and set to average.

[SharonHMA]

Hi,

The group by is a terrific function thank you. I didn't even realize it would get me part way there so easily.  So I have now grouped my Table1 set as desired - by Client & ProductGroup

The second half of the problem still remains though - which is I want to divide by the total count of all cleints in Table2 not Table1.

In other words:

I have 5600 total clients in Table1.  Of those clients, only 5200 bought product A (from Table2).  Total sales product A $117,000,000 (from Table 2)

I've created a measure: Average per client = DIVIDE([total sales],'Table2'[Total Clients')

Unfortunately when you create a visual by product it still gives you $117,000,000 / 5200, not $117,000,000 / 5600 which is what I want.  I want average sales for each product across all clients (ie my average client profile), not just those that bought individual products. So I need to either fix the denominator as total clients in Table 1 (regardless of filter from Table 2 although I might filter in table1) or I need a $0 value set if there is no sale to a client in a particular group.

[Eric_Zhang]

---

@SharonHMA wrote:

Hi,

The group by is a terrific function thank you. I didn't even realize it would get me part way there so easily.  So I have now grouped my Table1 set as desired - by Client & ProductGroup

The second half of the problem still remains though - which is I want to divide by the total count of all cleints in Table2 not Table1.

In other words:

I have 5600 total clients in Table1.  Of those clients, only 5200 bought product A (from Table2).  Total sales product A $117,000,000 (from Table 2)

I've created a measure: Average per client = DIVIDE([total sales],'Table2'[Total Clients')

Unfortunately when you create a visual by product it still gives you $117,000,000 / 5200, not $117,000,000 / 5600 which is what I want.  I want average sales for each product across all clients (ie my average client profile), not just those that bought individual products. So I need to either fix the denominator as total clients in Table 1 (regardless of filter from Table 2 although I might filter in table1) or I need a $0 value set if there is no sale to a client in a particular group.

---

@SharonHMA

"I need a $0 value set if there is no sale to a client in a particular group."

That is to say, the total clients is always a constant COUNTROWS(ALL(table2)).

You don't paste any definition of the measures [total sales] and [total clients], however, based on my understanding, below measure shall work in your case.

Average per client = DIVIDE(SUM(table1[Amount]),COUNTROWS(ALL(table2)))