Sum of max distinct values

[Anonymous]

Howdy all, I am looking for some help in summing distinct parent max values while filtering to a specific family, can y'all offer some guidence.

Example, I am wanting to only sum a single Usage value for each parent while also keeping the filter to each family (single family in example)

Parent	Family	Usage	Distinct Max Sum
ID07	5904	21	25
GR30	5904	0	25
GR30	5904	0	25
DL07	5904	2	25
DL07	5904	2	25
DL07	5904	2	25
6668	5904	2	25
6668	5904	2	25
5904	5904	0	25
7260	5904	0	25

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your onedrive business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

Hi @Anonymous 

You can use measure to achieve your goal.

Distinct Max Sum =

SUMX (

    SUMMARIZE (

        ALL ( 'Table' ),

        'Table'[Parent],

        "_max", CALCULATE (

            MAX ( 'Table'[Usage] ),

            FILTER (

                'Table',

                'Table'[Parent] = MAX ( 'Table'[Parent] )

                    && 'Table'[Family] = MAX ( 'Table'[Family] )

            )

        )

    ),

    [_max]

)

Result is as below:

You can download the pbix file from this link: Sum of max distinct values

Best Regards,

Rico Zhou

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

[amitchandak]

@Anonymous , Try a new measure

sumx(summarize(Table, Table[Family],Table[Parent],Table[Usage]),[Usage])

[Anonymous]

Thanks @amitchandak however this doesn't quite return the result as expected. If I use the measure in a single visual I get the output of 25, however I was hoping to return the value of 25 for each row in the dataset.