Dynamic average on X axis of a bar chart

[Anonymous]

Hi!

I have a data table with columns:

- date (date format)

- user ID (text format)

- number of transactions (made on that date by that user)

I want to create a bar chart that works well with a date slicer, so that the bars represent the average daily number of transactions made (by a player) in that period and their height is the count of users who had that average (rounded to a whole number) in the selected period. 

Does anyone have any idea how I could get this? Is it even possible in Power BI?

Thank you!

[AlB]

Hi @Anonymous 

I guess you mean a chart with bars and lines. Try this:

1. Place Table1[ID] in a Line and stacked column char visual 

2. Create these two measures (second one uses the firs one):

AvgMeas = AVERAGE(Table1[NumTrans])

NumUsersWithThisAvgNumTransactions =
VAR avg_ = [AvgMeas]
RETURN
    SUMX (
        ALL ( Table1[ID] );
        IF ( CALCULATE ( AVERAGE ( Table1[NumTrans] ) ) = avg_; 1; 0 )
    )

3. Place the first measure in "Column values" in the chart (displayed in the bars) and the second measure in "Line values" in the chart.

4. You can place the date in a slicer visual to specify the period of interest

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Anonymous]

Hi, 

so the problem is that Power BI doesn't let me put average of a value on X axis, it doesn't allow for any type of aggregations on X axis. 

The data that I have is grouped data, counting number of transactions for each user on each day. 

I could calculate the averages into another table, but then I would have to decide on a fixed period to consider. I want to be able to select a time period on a slicer, and when I do that I want for Power BI to calculate the daily averages of players transaction counts (some player have avg 1 transaction, some have 2 etc) and that should be on the x axis (1, 2, 3...) , while the height of the bar should show the count of players who fall into that avg category. 

Hope this helps. 

[amitchandak]

@Anonymous , Are you looking for sum of avg or avg of sum -

refer : https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Or something like this

Avg of Sum=
averagex(summarize(Table,Table[User],Table[Day], "_1",[Sum Measure]),[_1])

[Anonymous]

@amitchandak 

Yes, I want to get the average of a sum, exactly like in the measure you provided. But the problem is that Power BI doesn't allow for measures to be on the x axis (at least on bar charts and line charts)

[v-lili6-msft]

hi  @Anonymous 

Measure is dynamic summary value that depands on current row context, so you could not drag it into x-axis.

And for your case, it may could be achieved, please share some sample data and expected output, that will be a great help. 
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft, thank you for your advice. I'm sharing an example data table in csv format. The data is as I described in the first post, we have date, user id and number of transactions. The dates in the example are days in April. 

https://drive.google.com/open?id=1omUtaCgOzMP_y7eQq9M4nuhgZ3q95wQW

What I want to achieve is to have fixed whole numbers on the x axis (let's say from 1 to 50). Each of those numbers represents an average daily number of transactions. When I select a time period on a slicer (for example, I choose only the first week of April) I want to calculate the average number of transactions for each user but only considering that week, not the whole dataset. Then, I want the bar height of the bar 1 be the number of users who in that week had 1 trasaction on average. 

If we take user with ID 757 from the example table, his average in the whole month of April is 17. If we only look at the first week, he was active only on 3 days but made a lot of transactions then so his average in the first week would be 29. 

In the first case, when the slicer is set on the whole month, I want that user to extend the height of the bar for group 17 by 1, in the second case, when I'm looking only at one week, I want that user to extend the bar for 29 by 1. In other words, the height of the bar should be the number of users who had that average in that period. 

[v-lili6-msft]

hi  @Anonymous 

Ok, for your case, you could try this way as below:

Step1:

You need a separate table for x axis, so just try this formula to create a new table

x axis = GENERATESERIES(1, 50, 1)

Step2:

Then create a measure as below:

Measure = 
var _table=FILTER(GENERATE('x axis',SUMMARIZE(example,example[user_ID],"_average",INT(CALCULATE(AVERAGE(example[number_of_transactions]))) )),[Axis]=[_average]) return
COUNTROWS(_table)

Step3:

Now drag field from 'x axis' table into X-axis of visual and this measure into Value

here is sample pbix file, please try it.

Regards,

Lin