Forum Discussion
Compare aggregated values and select max
I need help to summarize the data for a category per user and count the results filtered by max value for each user For ex: In the below image, user - "SL-08" has an entry for category - H and 2 ...
Greg_Deckler . Thanks, I've tried a similar approach before and it doesn't seem to get the output i need, especially for users that have multiple categories in the select period. it selects both the categories for the user in this case.
VAR __summarytable =
// ADDCOLUMNS (
SUMMARIZE ( Test2
, Test2[User]
, Test2[Category Id]
, "MaxxCount",CALCULATE ( COUNTROWS(Test2))
, "TWCount",CALCULATE ( COUNTROWS(Test2), FILTER(Test2,Test2[Category Id] = 1))
, "AFCount",CALCULATE ( COUNTROWS(Test2), FILTER(Test2,Test2[Category Id] = 2))
, "VisitCount",CALCULATE ( COUNTROWS(Test2), FILTER(Test2,Test2[Category Id] = 4))
)
VAR __Max = MAXX(__summarytable,[MaxxCount])
RETURN
COUNTROWS(FILTER(__summarytable,[MaxxCount] = __Max || [AFCount] >= [TWCount]))
Greg_Deckler
Community Champion
Community ChampionAnonymous - Can you post sample data as text and expected output? So much easier to troubleshoot if I can recreate the problem locally.
Greg, V-lianl-msft,
Apologies, i created a duplicate post.
Greg_Deckler - please take a look at this thread. I've tried to explain the scenario and added sample data, with expected result
https://community.powerbi.com/t5/Desktop/Compare-counts-with-in-group-rank-and-count-the-top-values/m-p/1291186/highlight/true