Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
@sree_12 , 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |