cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imagautham
Helper II
Helper II

Finding Maximum number of transactions at the day level

Hello,

 

My data looks like the below. This is a sample data.

 

transaction.png

 

We could see on 1st Jan there is 5 Transactions because there are 5 rows for 1st Jan.

Similarly, for 2nd Jan there is 4 transactions

For 3rd Jan, there are 8 transactions.

I need to write a measure to see the number of transactions for each day at the day level and return the maximum number across

all days.

In the above case, it should return 8 because it is more than other day's transaction.

How do I write this?

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Create a proper date table and link that to your fact table. Then you can create a measure like

Num transactions = COUNTROWS('Fact table')

If you add that measure to a visual it with a column from your date table then it will show the number of transactions for that period.

To see the max number of transactions per day, you could create a measure like

Max num transactions =
SUMX (
    ADDCOLUMNS ( ALL ( 'Date'[Date] ), "@num transactions", [Num transactions] ),
    [@num transactions]
)

View solution in original post

2 REPLIES 2
imagautham
Helper II
Helper II

@johnt75 Thank you so much for this input. Only 1 slight change, I think we must use MAXX instead of SUMX

johnt75
Super User
Super User

Create a proper date table and link that to your fact table. Then you can create a measure like

Num transactions = COUNTROWS('Fact table')

If you add that measure to a visual it with a column from your date table then it will show the number of transactions for that period.

To see the max number of transactions per day, you could create a measure like

Max num transactions =
SUMX (
    ADDCOLUMNS ( ALL ( 'Date'[Date] ), "@num transactions", [Num transactions] ),
    [@num transactions]
)

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors