Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
yas82
Regular Visitor

Average number of transaction by members

Hi, 

I am very new in DAX . I am trying to find out the average of transactions by members. I tried this formula CALCULATE(DISTINCTCOUNT(fTransactions[# négo]);fTransactions) to find out the total of members for each division but I got the wrong number because one member has register no transaction.

2 ACCEPTED SOLUTIONS
mark_endicott
Super User
Super User

@yas82 - DAX has an iterating function for exactly this calculation. If you have something that idendifies your members you can try this in a measure (use your column names and calculation):

 

 

AVERAGEX( VALUES( Table[Member Identifier] ), [Transactions] ) 

 

 

This iterates through the members and will average the each of their transactions, so it will work at a total and member level. It will also work against other fields in your model. 

 

If this works, please accept as the solution it helps with visibility for others. 

 

 

View solution in original post

DISTINCTCOUNT( member foreign key ) - this can be placed against any field in your model (such as columns from your division table) to establish how many members appear in your transaction table for that field.

 

It can also be used for a grand total. 

 

This assumes you have a correct relationship set up between Transactions and your Dimension tables. 

View solution in original post

7 REPLIES 7
raji_n
Resolver I
Resolver I

@yas82 use allexcept(mewmber) n your DAX

mark_endicott
Super User
Super User

@yas82 - DAX has an iterating function for exactly this calculation. If you have something that idendifies your members you can try this in a measure (use your column names and calculation):

 

 

AVERAGEX( VALUES( Table[Member Identifier] ), [Transactions] ) 

 

 

This iterates through the members and will average the each of their transactions, so it will work at a total and member level. It will also work against other fields in your model. 

 

If this works, please accept as the solution it helps with visibility for others. 

 

 

I have 2 dimensions tables the first one has the Division and the second one has the member as primary key and the divsions is in the this table. I have the fact table, my transaction table. I have less members in my transaction table in total than in the dimension table because I have one member that registered 0 transactions. I am using the divide function to calculate my average so I need to find out the total of members including the one with no transaction but my total gave me on less. In total I have 51 and should get 52.

@yas82 - As mentioned, I would use AVERAGEX( ) and use the member primary key inside VALUES( ), you do not then need to worry about the member with 0 transactions. 

 

This calculation will also work against your Division table so long as the [Transactions] Measure is something along the lines of COUNTROWS( Transactions )

and if I want calculate just the total of members by division and the grand total ?

DISTINCTCOUNT( member foreign key ) - this can be placed against any field in your model (such as columns from your division table) to establish how many members appear in your transaction table for that field.

 

It can also be used for a grand total. 

 

This assumes you have a correct relationship set up between Transactions and your Dimension tables. 

If I have 20 memebers  in my dimension table for division 1 but in the fTransaction I have 17 left, with the Distinctcount function I will get the 17 members from the division 1. Both Division and Members tables dont have a relationship but both connect with the ftransaction table.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.