Average number at a given time

[JohnW]

Hi everyone,

I am still kind of new to Power BI so hopefully this is an easy one to help with.

I am trying to get the average number of swipe counts by date to show upper management what they can expect for an average number of customers per day.  The problem is that when I try to change the value from SUM to AVERAGE the values flatline all at 1.  It does this because for each customer it counts 1 swipe.  So when it tries to get the average it's just counting all the swipes and dividing by the number of them which would ultimatley be 1.

So my question would be is there a way within Power BI that would let me be able to get the right average for each day without it getting an average of 1?

Thanks!

With SUM function

[amitchandak]

@JohnW , Try like

AverageX(Values(Table[Date]),sum(Table[Swipe]))

In case you already have a measure

AverageX(Values(Table[Date]),[Swipe count])

[JohnW]

Thank you for the reply @amitchandak,  

I tried both of your suggestions and the results are the same as when I have the Swipe Counts set to SUM.  

[amitchandak]

@JohnW ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[JohnW]

@amitchandak,

Is this what you are looking for for the sample data and output? 

Sample Table

ID	Swipe Count	Date
1	1	9/12/2019
1	1	9/12/2019
4	1	9/12/2019
4	1	9/12/2019
5	1	9/12/2019
5	1	9/12/2019

Output Table

Average Swipe Count	Date
3	9/12/2019

I think this would work if there is a way in Power BI to group the IDs and see how many swipes they had in a single day if that makes sense?

[v-zhenbw-msft]

Hi @JohnW ,

If you want to calculate the number of customers per day, you can try this measure.

Average Swipe 1 = DISTINCTCOUNT('Table'[ID])

If you want to calculate the average of swipe based on customer, you can try this measure.

Average Swipe 2 = DIVIDE(SUM('Table'[Swipe Count ]) , DISTINCTCOUNT('Table'[ID]))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@JohnW , this seem like distinctcount

AverageX(Values(Table[Date]),distinctcount(Table[ID]))

[JohnW]

Thank you @amitchandak and @v-zhenbw-msft !

Those functions worked.  Thank you agian for all the help!