Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Max Total

Hi All,

 

I want to have grand total of Max values

 

Here is how data set looks like

 

Data set sample

sometimes i have 2 dates, sometimes 3 dates, but target needs to be same, hence Iam struggling to use "x" functions, like sumx and maxx.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    msksenthil 

     

    dont know if this is bloated, i kinda got it working with this

    i forgot about summarise earlier

     

    Measure =
    Var MaxTarget = maxx('Staff Daily Utilisation','Staff Daily Utilisation'[Target Max As per current working Days])
    Var MaxTargetTotal = SUMMARIZE('Staff Daily Utilisation','Staff Daily Utilisation'[Staff Name],"Max Total",MaxTarget)
    Return
    if(HASONEVALUE('Staff Daily Utilisation'[Staff Name]),
    MaxTarget,
    SUMX(MaxTargetTotal,[Max Total]))

8 Replies

  • msksenthil's avatar
    msksenthil
    Helper III
    5 years ago

    Anonymous  can you try the below


    SUMX ( DISTINCT ( 'TableName' ), 'TableName'[FieldName] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      msksenthil 

      tnks

      I have tried this before,  since all values are distinct, just that some data is missing everyday and i want to put max regardless

  • FrankAT's avatar
    FrankAT
    Community Champion
    5 years ago

    Hi Anonymous 

    you can do it like this:

     

     

    Sum of Distinct Target Hours = 
VAR _Sum = SUM('Table'[Target Hours])
VAR _Table = DISTINCT('Table'[Target Hours])
RETURN
    IF(ISINSCOPE('Table'[Date]),_Sum, SUMX(_Table,'Table'[Target Hours]))

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

  • msksenthil's avatar
    msksenthil
    Helper III
    5 years ago

    @luisClive Thanks,

    I believe you have many fields with different values on each record, in this case, we have calculated by not considering the other fields except the required fields like 'date' & 'Target Hours'

    SUMX (
    SUMMARIZE ( Maxhours, Maxhours[Date], "Hours", MAX ( Maxhours[Target hours] ) ),
    [Hours]
    )

    Hope this helps,

    MS

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      msksenthil tnks let me try Summarize function, let me check it out

      FrankAT thanks, i havent used inscope before, i check this too

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      msksenthil 

       

      dont know if this is bloated, i kinda got it working with this

      i forgot about summarise earlier

       

      Measure =
      Var MaxTarget = maxx('Staff Daily Utilisation','Staff Daily Utilisation'[Target Max As per current working Days])
      Var MaxTargetTotal = SUMMARIZE('Staff Daily Utilisation','Staff Daily Utilisation'[Staff Name],"Max Total",MaxTarget)
      Return
      if(HASONEVALUE('Staff Daily Utilisation'[Staff Name]),
      MaxTarget,
      SUMX(MaxTargetTotal,[Max Total]))