cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

@ahmedoye 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Getting the sum of maximum distinct values

Hi, @prakash_9471 

 

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

Table:

g1.png

 

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:

g2.png

 

Best Regards

Allan

 

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

View solution in original post

4 REPLIES 4
Highlighted
Community Champion
Community Champion

Re: Getting the sum of maximum distinct values

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

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
Community Champion
Community Champion

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

Highlighted
Community Support
Community Support

Re: Getting the sum of maximum distinct values

Hi, @prakash_9471 

 

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

Table:

g1.png

 

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:

g2.png

 

Best Regards

Allan

 

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

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors