Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Table Help Pls

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 shou...
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

     
AlB's avatar
AlB
Community Champion
6 years ago

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