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

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
Icon for Microsoft Employee rankMicrosoft 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