Returns a summary table over the most common values between categories

[THPNO80]

Hi there!

I´m trying to get a summary table over the most accessed reports between two or more categories (by function) in a "Log Events" table. The results will be helpful to create a cleaner network analysis chart. See the example below:

User	Function	Report	Log_Events
A	Marketing	Report_A	5
A	Marketing	Report_B	6
A	Marketing	Report_C	2
B	Account	Report_D	1
B	Account	Report_A	8
C	CS	Report_B	9
D	Finance	Report_B	10
D	Finance	Report_A	2
E	R&D	Report_C	3

Expected outcome:

Report	Functions	Log_Events
Report_A	3	15
Report_B	3	25
Report_C	2	5
Report_D	1	1

I hope you can help me... thanks in advance!

[tackytechtom]

Hi @THPNO80 ,

How about this:

Here the code for creating a new table in DAX:

DerivedDaxTable = 
SUMMARIZE (
    'Table',
    'Table'[Report],
    "Functions",  COUNT ( 'Table'[Function] ), 
    "Log_Events", SUM ( 'Table'[Log_Events] )
)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[THPNO80 · Accepted solution]

Hi @tackytechtom I really appreciate your help, it worked! but when applied to the model it didn't get the expected result. I'm missing something else 🤔 Thanks anyway 👍

[serpiva64]

Hi,

try

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[THPNO80]

Hello @serpiva64 I really appreciate your help too, but, as I commented above, I didn't get the expected result in the model... Regards! 👍