Average number of transaction by members

[yas82]

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.

[mark_endicott · Accepted solution]

@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. 

[raji_n]

@yas82 use allexcept(mewmber) n your DAX

[mark_endicott · Accepted solution]

@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. 

[yas82]

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.

[mark_endicott]

@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 )

[yas82]

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

[mark_endicott · Accepted solution]

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. 

[yas82]

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.