Power BI / DAX - Stacked Column Chart with Dynamic Count

Question

I have a Customers table which looks like the following:

Customers

Name         ID           Number of Purchases
Billy        100          2
Steve        101          1
Alison       102          5
John         103          3
Matt         104          0
Andrew       105          1

I have a standard Date dimension

 Date          Month        Year
 1/1/2017      January      2017
 1/2/2017      January      2017
 1/3/2017      January      2017
 1/4/2017      January      2017
 ...
 2/1/2017      February     2017
 2/2/2017      February     2017
 2/3/2017      February     2017
 2/4/2017      February     2017

I have a Purchases table

Purchases

 Date         Name      ID       Amount
 1/1/2017     Billy     100      10.53
 1/1/2017     Alison    102      15.90
 1/2/2017     Alison    102      9.87
 1/3/2017     Steve     101      12.59
 1/4/2017     Billy     100      22.19
 1/4/2017     John      103      17.45
 1/5/2017     John      103      8.79
 2/1/2017     Alison    102      9.87

I am trying to develop something where every customer get's thrown into a bin based on the number of purchases they have made in the selected date range.

So if I select 1/1/2017 - 1/2/2017, I should only have 2 bars. The first bar should have a 1 on the x axis with a value of 1 because "Billy" made 1 purchase in the selected time period, and the second bin will be 2, and it will also have a value of 1 because "Alison" made 2 purchases in the selected time period. IF I put [Number of Purchases] on the x-axis and then use

NameCount = CALCULATE( COUNT(Purchases[ID]), FILTER(Purchases, Purchases[ID] = RELATED(Customer[ID])))

for the values, it tells me there are 3 people with 5 purchases, but that is not true. In reality, no one actually has 5 purchases on the Purchases table. Alison is in the lead with 3 purchases.

To accomplish this I am planning on using a stacked column chart, I just don't know what to use for the Axis and Values. The Axis only allows me to use a static column, which would be fine, but I could have customers with 50 purchases.

I think the answer is going to be to develop a calculated table, but I've never done that before so I'm not sure how/if that will work.

dybzon dybzon · Accepted Answer · 2017-07-15T15:26:20

There are a few things that need to be changed to accomplish what you want. I'll try to explain what is happening in your scenario, and then provide some input to what you might change.

Why do you get this result?

When you set your date filter to 1/1/2017 - 1/2/2017 you have the following purchases left in scope (both the selected dates in your filter are included):

Date         Name      ID       Amount
1/1/2017     Billy     100      10.53
1/1/2017     Alison    102      15.90
1/2/2017     Alison    102      9.87
2/1/2017     Alison    102      9.87

When you set up a barchart with Customer[Purchases] on the X-axis, and your measure

NameCount = CALCULATE( COUNT(Purchases[ID]), FILTER(Purchases, Purchases[ID] = RELATED(Customer[ID])))

on the Y-axis, then you are effectively counting the number of [ID]s from the Purchases table and displaying this for the related [Purchases] value from the Customer table.

Alison has 5 purchases, and Billy has 2 purchases, according to your Customer table. These are static values, and are not affected in any way by your date filter.

The COUNT() function does not return a distinct number of [ID]s (you can use the DISTINCTCOUNT() function to achieve this). Therefore you will see the value 3 on the Y-axis (Alison appears three times in the Purchases table for the selected dates) for the value 5 on the X-axis (Alison has 5 purchases according to your Customer table). The same thing goes for Billy.

What can you change to accomplish what you want?

What you are trying to accomplish is not easily possible. You want to use a dynamic measure (the number of purchases for each customer in the selected period) on one axis, and another dynamic measure (the distinct number of customers in the selected period) on the other axis. This cannot be done in PowerBI. The problem here is the dynamic date filter, which stops you from pre-calculating the number of purchases as a calculated column.

One solution could be to create a new table that contains the number of purchases per customer per month. You could then use a filter on one month only, and show the number of purchases per customer for that month on the X-axis. This would be possible because the number of purchases is then a pre-calculated number (a column, not a measure). On the Y-axis you could use a measure like this:

CustomerCount = DISTINCTCOUNT(PurchasesPerMonth[CustomerID])

Power BI / DAX - Stacked Column Chart with Dynamic Count

1 Answers