topic Re: Average number of transaction by members in DAX Commands and Tips

Average number of transaction by members

Anonymous — Thu, 22 Aug 2024 14:08:22 GMT

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.

Re: Average number of transaction by members

mark_endicott — Thu, 22 Aug 2024 15:12:42 GMT

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

Re: Average number of transaction by members

raji_n — Thu, 22 Aug 2024 15:12:17 GMT

Anonymous use allexcept(mewmber) n your DAX

Re: Average number of transaction by members

Anonymous — Thu, 22 Aug 2024 15:57:09 GMT

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.

Re: Average number of transaction by members

mark_endicott — Thu, 22 Aug 2024 16:48:18 GMT

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

Re: Average number of transaction by members

Anonymous — Thu, 22 Aug 2024 17:10:11 GMT

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

Re: Average number of transaction by members

mark_endicott — Thu, 22 Aug 2024 17:59:25 GMT

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.

Re: Average number of transaction by members

Anonymous — Fri, 23 Aug 2024 18:48:54 GMT

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.