Max Total

[Anonymous]

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.

[msksenthil]

@luisClive I used Measure to calculate the numbers

[msksenthil]

@luisClive Sure 👍

[Anonymous · Accepted solution]

@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]))

[msksenthil]

@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]

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

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

[FrankAT]

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]

@Anonymous  can you try the below

SUMX ( DISTINCT ( 'TableName' ), 'TableName'[FieldName] )

[Anonymous]

@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