Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

ahmedoye 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        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