Getting the sum of maximum distinct values

[Anonymous]

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

@ahmedoye 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

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.

[mahoneypat]

Please try this expression

Sum of Max Sales per Store per Week =
VAR __summarytable =
    ADDCOLUMNS (
        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

[Anonymous]

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

[mahoneypat]

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 =
    ADDCOLUMNS (
        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