Table Help Pls

[Anonymous]

Hi all, another little question please. There is the following data below. Need to add all the "ETC" by Month and Name and then divide by "Avail". 

So for example, for Sam in Jan, the result should be (10+30+20)/160. Thanks very much!

 

[mahoneypat · Accepted solution]

Here is one way to get your desired result with a measure (if your visual includes person and month columns) or a DAX calculated column.

Result =
VAR sumest =
CALCULATE (
SUM ( Avail[Est] ),
ALLEXCEPT ( Avail, Avail[Month], Avail[Person] )
)
VAR sumavail =
CALCULATE (
SUM ( Avail[Avail] ),
ALLEXCEPT ( Avail, Avail[Month], Avail[Person] )
)
RETURN
sumest / sumavail

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Thanks! That worked!

[AlB]

Hi @Anonymous 

Do you want this in DAX or the query editor. You seem to be showing the query editor interface but this would be easier/more natural in DAX. It can also be done in M though.  Can you paste the data here in text-tabular format (instead of screen capture) so that it can be copied and used in a test?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers