count, avg, max, min of transactions per day

[pborah]

I have two columns - transaction number and transaction date. There are transactions happening every single day including weekends.

I want to show the following information in individual cards by calculating one measure - transactions per day using the fundtion -(Calculate(Count(table[transaction number]),AllExcept(table, table[transaction date]))

- avg transactions per day

- max transactions per day

- min transactions per day

There is a Yearly Quarters filter on report level. For Q12020 there were 91 days since 2020 is a leap year. However the average metric that I get is for 90 days. There were 40783 transactions for the specific quarter. That divided by 91 yields 448, but the metric on the card is 452 which is the total divided by 90. I went back to the sql db to see if there was any issues with the lookup table that assigns quarter to dates, everything is fine there, it shows Q12020 as having 91 days. Interesting but obvious find - Datediff between the starting date and ending date yields 90 days but a distinct count of transaction dates yields 91. Creating a new table for solving this issue  using SUMMARIZE results in metrics that are completely off, so I would like to avoid that if possible. Basically what I would like is a simple way of calculating transactions per day as a measure so that I can drop that into 3 cards and choose avg, max, and min of that. It's so easy to do in SQL but I've been stuck on this for days now PowerBI. Thank you very much.

[amitchandak]

@pborah , Try like

maxx(values(Table[Date]),countrows(Table))

minx(values(Table[Date]),countrows(Table))

averagex(values(Table[Date]),countrows(Table))

[pborah]

@amitchandak , I tired out the solution you provided for average, however that actually gave me total transactions for the quarter when I dropped that measure on to a card.

Besides, I just want to calculate total transactions per day. Then I can drop that number on three different cards and have each of them display the avg, min, and max of total transactions per day by choosing the appropriate aggregation under Field settings. Thank you.

[parry2k · Accepted solution]

@pborah This is what you need in measures and use in card visual

Base Measure = COUNTROWS ( Table )

Max = maxx(values(Table[Date]),[Base Measure] )

Min = minx(values(Table[Date]),[Base Measure]

Avg = averagex(values(Table[Date]),[Base Measure]

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[pborah]

@parry2k that works beautifully thank you!