cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Getting the sum of maximum distinct values

Hi,

I have the data in the below format.

 Storenumber FW Period CUST Sales OP 1 FW2 PRE REP 12356 1 FW2 PRE NEW 12356 1 FW3 PRE REP 20000 1 FW3 PRE NEW 20000 2 FW2 PRE REP 10000 2 FW2 PRE NEW 10000 2 FW3 PRE REP 80000 2 FW3 PRE NEW 80000

For every store, I have two types of customers for a week. Now, I need to get the total sales OP for PRE PERIOD without adding the duplicates.

For example, sales OP for PRE-PERIOD should be 123456 + 20000+10000+80000.

I have considered the below approach but was unsuccessful.

I need to get the maximum sales op/store/week and add all the maximum values to get the total sales op. I have used the below DAX, but was unsuccessful.

_wk_2_10_OP =
Salesnet

CALCULATE(SUMX(VALUES(cust_df[Period]) ,CALCULATE( MAX(cust_df[OP]),VALUES ( cust_df[storenumber] ),
VALUES ( cust_df[FiscalYearWeek] ))),
FILTER (cust_df,
cust_df[Period] = "PRE" ))

PS: There are chances that duplicate records can be present for the same customer type. Salesnet column is not present in sample data.

eg: The below record can be repeated twice, but 123456 should be added only once.

 1 FW2 PRE REP 12356

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Getting the sum of maximum distinct values

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

``````Result =
var tab =
GROUPBY(
'Table',
'Table'[Storenumber],
'Table'[FW],
'Table'[Period],
"Max",MAXX(CURRENTGROUP(),'Table'[Sales OP])
)

return
DIVIDE(
SUMX(
FILTER(
tab,
[Period]="PRE"
),
[Max]
),
SUMX(
FILTER(
tab,
[Period]="POST"
),
[Max]
)
)``````

Result:

Best Regards

Allan

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

4 REPLIES 4
Highlighted
Super User VI

## Re: Getting the sum of maximum distinct values

Please try this expression

``````Sum of Max Sales per Store per Week =
VAR __summarytable =
SUMMARIZE ( Table, Table[FW], Table[Storenumber] ),
"@maxsales", CALCULATE ( MAX ( Table[Sales OP] ) )
)
RETURN
SUMX ( __summarytable, [@maxsales] )``````

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!

Highlighted
Regular Visitor

## Re: Getting the sum of maximum distinct values

Hi Pat,

Thanks for the reply.

Can it be done without creating another column? I want to divide that sum(maximum values for all stores) by another value and display that value as Card(123). In the sample data, I have provided only PRE Period but I have got Post Period also.

Regards

Prakash

Highlighted
Super User VI

## Re: Getting the sum of maximum distinct values

The expression I provided is for a measure so no extra column needed.  If you want to compare your PRE and POST, you could adapt the measure like this, for example:

``````PRE vs POST Max Sales per Store per Week =
VAR __summarytable =
SUMMARIZE ( Table, Table[FW], Table[Storenumber], Table[Period] ),
"@maxsales", CALCULATE ( MAX ( Table[Sales OP] ) )
)
VAR __PRE =
SUMX ( FILTER ( __summarytable, Table[Period] = "PRE" ), [@maxsales] )
VAR __POST =
SUMX ( FILTER ( __summarytable, Table[Period] = "POST" ), [@maxsales] )
RETURN
DIVIDE ( __PRE, __POST )``````

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!

Highlighted
Community Support

## Re: Getting the sum of maximum distinct values

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

``````Result =
var tab =
GROUPBY(
'Table',
'Table'[Storenumber],
'Table'[FW],
'Table'[Period],
"Max",MAXX(CURRENTGROUP(),'Table'[Sales OP])
)

return
DIVIDE(
SUMX(
FILTER(
tab,
[Period]="PRE"
),
[Max]
),
SUMX(
FILTER(
tab,
[Period]="POST"
),
[Max]
)
)``````

Result:

Best Regards

Allan

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

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors