Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 ...
Need Help
V-lianl-msft's avatar
V-lianl-msft
Community Support
6 years ago

Hi Anonymous ,

 

Based on your description, F has a priority, so you can first create a calculated column like this:

rank =
VAR count_catefory =
    CALCULATE (
        COUNT ( 'Table (2)'[Category Id] ),
        ALLEXCEPT (
            'Table (2)',
            'Table (2)'[User],
            'Table (2)'[Wk],
            'Table (2)'[Category Name]
        )
    )
VAR precedence =
    IF ( 'Table (2)'[Category Name] = "F", count_catefory + 0.5, count_catefory )
RETURN
    precedence

Then create a calculated column to get the category name of each user.

category_test =
VAR max_ =
    CALCULATE (
        MAX ( 'Table (2)'[rank] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[User] )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Table (2)'[Category Name], 1 ),
        FILTER ( 'Table (2)', 'Table (2)'[rank] = max_ )
    )

The third graph cannot be achieved in power Bi for the time being

Sample .pbix

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi V-lianl-msft. Thank you! Area graph shown in the final result is what i really need and i would like to use measures to make it dynamic because it needs to return data for period selected month/week etc, actual data set is over 8M+ rows 

    Also, I've added the "week" and "category Name" columns for ease. They both have different tables