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.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft 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
      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