The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello I have a table with customer sales lines, and each row is assigned to an agent (numerico). I need to take out a measure that is fixed, that is the average of the sum of the number of (distinct) clients of the agents that I specify.
Example: I want the average number of clients of agent 23, 24 and 25
agent 23 has (distinct) clients = 50
agent 24 has (distinct) clients = 70
agent 25 has (distinct) clients = 60
media = 180/3 = 60
and q that average does not vary as long as I do not change the codes of the agents by code, or add/ remove agents.
I'm trying with averagex but I can't get it.
Thank you.
Solved! Go to Solution.
Hi @ivanmoreno ,
I created a sample pbix file(see attachment), please check whether that is what you want. I created two measures as below, please check which one is your expected result.
average of agents =
VAR _tab =
SUMMARIZE (
'Table',
'Table'[agent],
"@coutofclients", CALCULATE ( DISTINCTCOUNT ( 'Table'[client] ) )
)
RETURN
AVERAGEX ( _tab, [@coutofclients] )
avarage 2 =
VAR _sumofclients =
CALCULATE ( DISTINCTCOUNT ( 'Table'[client] ), ALL ( 'Table' ) )
VAR _countofagent =
CALCULATE ( DISTINCTCOUNT ( 'Table'[agent] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( _sumofclients, _countofagent )
Best Regards
AverageX(values(Table[agent]), Calculate(distinctcount(Table[clinet] ) ) )
Hello, so I get the average of customers of all agents, but I need to specify the agents I want
Hi @ivanmoreno ,
I created a sample pbix file(see attachment), please check whether that is what you want. I created two measures as below, please check which one is your expected result.
average of agents =
VAR _tab =
SUMMARIZE (
'Table',
'Table'[agent],
"@coutofclients", CALCULATE ( DISTINCTCOUNT ( 'Table'[client] ) )
)
RETURN
AVERAGEX ( _tab, [@coutofclients] )
avarage 2 =
VAR _sumofclients =
CALCULATE ( DISTINCTCOUNT ( 'Table'[client] ), ALL ( 'Table' ) )
VAR _countofagent =
CALCULATE ( DISTINCTCOUNT ( 'Table'[agent] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( _sumofclients, _countofagent )
Best Regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
142 | |
109 | |
107 | |
76 | |
61 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |