topic Re: Table Help Pls in DAX Commands and Tips

Table Help Pls

Anonymous — Sun, 24 May 2020 19:33:14 GMT

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!

Re: Table Help Pls

AlB — Sun, 24 May 2020 20:05:41 GMT

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

Re: Table Help Pls

mahoneypat — Sun, 24 May 2020 20:15:25 GMT

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

Re: Table Help Pls

Anonymous — Sun, 24 May 2020 21:04:43 GMT

Thanks! That worked!