Count values that equal to Maximum per category.

[dvavrous]

Hello, 

hope I can get some help. 

I need to count only the values that are equal to maximum value per that category_2. It has to be measure as I want to use multiple slicers. Each time I filter the data by slicer the maximum changes.

Idcategory_1category_2evaluationslicer

1	Bikes	City Bike	0	Idea
2	Bikes	City Bike	1	Impl
3	Bikes	City Bike	2	Impl
4	Bikes	City Bike	2	Impl
5	Bikes	City Bike	2	Impl
6	Bikes	Mountain Bike	0	Idea
7	Bikes	Mountain Bike	0	Idea
8	Bikes	Other	0	Idea

I feel I might be close with this measure but it is not quite right. 

count_max =

COUNTX (

    FILTER (

        'Table',

        CALCULATE (

            MAXX (

                SUMMARIZE (

                    ALLSELECTED ( 'Table' ),

                    'Table'[category_1],

                    'Table'[category_2],

                    "max_evaluation", CALCULATE ( MAX ( 'Table'[evaluation] ) )

                ),

                [max_evaluation]

            ),

            ALLEXCEPT ( 'Table', 'Table'[category_2], 'Table'[slicer] )

        ) = 'Table'[evaluation]

    ),

    COUNT ( 'Table'[category_2] )

)

It gets me to this: 
- the issue is with the total for category_1 (Bikes) that does not equal the maximum value for all.

Evaluation	0	2
Bikes	(blank)	3
City Bike		3
Mountain	2
Other	1

Expcted Result

Evaluation	0	2
Bikes	3	3
City Bike		3
Mountain	2
Other	1

Sample file

https://drive.google.com/file/d/1xZ7gv80k_l90SI6-RNRn83UTA8fhcPir/view?usp=sharinghttps://drive.goog...

Any help will be appreciated

[Anonymous]

Hi, @dvavrous 

Based on the PBIX file you provided, I created a new measure using the following DAX expression:

MEASURE =
VAR _seleted =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[evaluation] ),
        FILTER (
            'Table',
            CALCULATE (
                MAXX (
                    SUMMARIZE (
                        ALLSELECTED ( 'Table' ),
                        'Table'[category_1],
                        'Table'[category_2],
                        "max_evaluation", CALCULATE ( MAX ( 'Table'[evaluation] ) )
                    ),
                    [max_evaluation]
                ),
                ALLEXCEPT ( 'Table', 'Table'[category_2], 'Table'[slicer] )
            ) = 'Table'[evaluation]
        )
    )
RETURN
    IF (
        NOT ISINSCOPE ( 'Table'[category_2] ),
        SUMX ( FILTER ( 'Table', 'Table'[evaluation] = _seleted ), [count_max] ),
        COUNTX (
            FILTER (
                'Table',
                CALCULATE (
                    MAXX (
                        SUMMARIZE (
                            ALLSELECTED ( 'Table' ),
                            'Table'[category_1],
                            'Table'[category_2],
                            "max_evaluation", CALCULATE ( MAX ( 'Table'[evaluation] ) )
                        ),
                        [max_evaluation]
                    ),
                    ALLEXCEPT ( 'Table', 'Table'[category_2], 'Table'[slicer] )
                ) = 'Table'[evaluation]
            ),
            COUNT ( 'Table'[category_2] )
        )
    )

Here are the results:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[dvavrous · Accepted solution]

Hello Jianpeng Li, thank you very much for your help.

It worked on the data I sent but it did not work on my original data. 
I must have made a mistake in my original measure that I shared.

Anyway I was able to come up with a different approach that worked.

For anyone interested see measure below.

MEASURE =
VAR _max_table =
SUMMARIZE (
ALLSELECTED ( 'Table 2' ),
'Table 2'[category_1],
'Table 2'[category_2],
"max_evaluation", CALCULATE ( MAX ( 'Table 2'[evaluation] ) )
)
VAR _values =
SELECTCOLUMNS (
'Table 2',
"category_1", 'Table 2'[category_1],
"category_2", 'Table 2'[category_2],
"evaluation", 'Table 2'[evaluation]
)
VAR _inter =
INTERSECT ( _max_table, _values )
RETURN
COUNTROWS ( _inter )

[Anonymous]

Hi, @dvavrous 
Thank you very much for your reply. Did the DAX expression you shared solve your current problem?

Best Regards

Jianpeng Li