Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello Team,
I need your suggestions in creating Average value for Distinct count of ID's after summarizing three columns in a table.
ID | Category | Code | Date | Subject | Type |
1 | A | X | 7/20/2020 | ABC | Sales |
1 | A | Y | 7/21/2020 | ABC | Sales |
2 | B | Z | 7/20/2020 | DEF | Purchase |
3 | B | Z | 7/22/2020 | DEF | order |
4 | B | Z | 7/23/2020 | XYZ | order |
What I need is to Create Calculated column/ measure which gets Average of distinct count of ID column after summarizing Category, code, date, subject columns.
Please suggest me dax code for this,
Thank you,
@Anonymous , depending on the need
averageX(summarize(Table,Table[Category],Table[code],Table[date], Table[Subject],"_1",distinctCOUNT(Table[ID])),[_1])
averageX(summarize(Table,Table[Category],Table[code],Table[date],"_1",distinctCOUNT(Table[ID])),[_1])
averageX(summarize(Table,Table[Category],"_1",distinctCOUNT(Table[ID])),[_1])
averageX(summarize(Table,Table[code],Table[date],"_1",distinctCOUNT(Table[ID])),[_1])
averageX(summarize(Table,Table[date], Table[Subject],"_1",distinctCOUNT(Table[ID])),[_1])
Thank you @amitchandak
I am not getting expected result when I apply this solution to my report
Thanks you
Hi @Anonymous ,
Can you share the expected output ?
Regards,
Harsh Nathani
Please try this in a measure expression
Avg ID Count =
AVERAGEX (
SUMMARIZE ( Table, Table[Category], Table[Code], Table[Date], Table[Subject] ),
CALCULATE ( DISTINCTCOUNT ( Table[ID] ) )
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You can use this formula to get your results
ID DistinctCount = CALCULATE(Distinctcount(Table[ID]), ALLEXCEPT(Table, Table[ID], Table[Type]))
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thank you @mahoneypat .
But I need to calculate average(Distinctcount(Table[ID])) after summarizing above mentioned columns.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |