Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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.
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 - 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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
19 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
22 | |
22 |