I need correct total of sum divided by max

[Anonymous]

Hi, guys.

In my model I have a table with data I need summarized by date (basicaly, sum of all data on a given day) and I need that measure divided by max value from the rows (from the same table) for the same day, and all this for each worker who worked on that day.

Basicaly, it looks like this Goal = Divide(SUMX(table 1, column 1),MAXX(table 1, column 2)

Table can be something like this: 

Date      (1)      (2)       (3)

01.10.   100     50       Name 1

01.10.   200     50       Name 2

01.10.   300     100     Name 1

01.10.    50      30       Name 2

02.10.   100     120     Name 1

02.10.   400     100     Name 2

03.10.   500     100     Name 1

So, I need for 01.10. Sum(1)/Max(2) = 400/100+250/50=150 and that to apply for each day.

Also, I need a correct total, because every time I make a measure, it sums me only per days (the measure don't diferentiate the workers).

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here’s my solution.

1.Create a measure for each name in one day.

2.Create the final goal.

Best Regards,
Community Support Team _ kalyj

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

[JMD22Si]

I have a very similar table (Store, Service, revenue, date) and desired result.   I've used your example, but all i end up with is "1"s

percentile 2 = var _sum=Calculate(sum(PharmacyServices[ServicesRevenue]),FILTER(ALLSELECTED(PharmacyServices),[ServiceDescription]=MAX(PharmacyServices[ServiceDescription])&&[Date]=MAX(PharmacyServices[Date]))) Var _Max=Calculate(Max(PharmacyServices[ServicesRevenue]),Filter(ALLselected(PharmacyServices),[ServiceDescription]=MAX(PharmacyServices[ServiceDescription])&&[Date]=MAX(PharmacyServices[Date]))) Return DIVIDE (_sum,_max)

Table with slicer applied for store 522

Here's the raw data for the first Service.   The result of the calculation IMO should be 14/70 = .2 or 20%

Any help is greatly appreciated! 

[Anonymous]

Thank you! 

That did the trick.

[speedramps]

Hi sabreGEA

It might just be me, but I dont undestand your problem.  Please can you try explain it more clearly. Thanks

In the meantime these measure may help .you understand how to get a max value for name or date.

Remember we dont get paid, we are BI community voluntrees so please click the thumbs-up for me taking the trouble to help you and then accept the solution if it works.  Thank you !

Max value =
MAX('Table'[Value1])

Max value for name =
CALCULATE(
MAX('Table'[Value1]),
ALLEXCEPT('Table','Table'[Name])
)

Max value for date =
CALCULATE(
MAX('Table'[Value1]),
ALLEXCEPT('Table','Table'[Date])
)

[Anonymous]

Hi, speedramps .

Thank you for your help. 

I'm attaching the screenshot with my issue. 

So, I'm having dates, workers names, column 1 (sum of working hours per date), column 2 (max of possible working hours per day) and column where I divided columns one and two. 

I need the correct total for max values (highlighted red).

[speedramps]

Hi again

This measure gets the max value for all 

Max value for all  =

CALCULATE(

MAX('Table'[Value1]),

ALL()

)

[Anonymous]

That measure just included workers I filtered earlier and total of max column is still wrong.

var summax = MAXX(table 1,value 1)

RETURN

IF(HASONEVALUE(table 1, date column),

summax,

SUMX(VALUES(table 1, date column),summax))

This is the measure I tried with no avail.

[speedramps]

I'm sorry, I dont understand.

See the forum for tips on how to get your question answered.

Good luck.