Solved: How to choose any column and sum up the values?

Harry_Ikeda · 09-18-2021 06:24 PM

Hi everyone.

I’d like to this but have difficulties to work out ;

Table1
Date A B C
*** 1 2 3
*** 1 2 3
*** 1 2 3

Action

Choose A, B or C by dropdown (completed)
Choose start date and end date (completed)

3 Sum up the chosen item ex. When I choose A, the answer is 3 ( When B, 6)

I already know SELECT(Table1[A], AND([Date] >= [_THISROW].[start].[Date] <= [_THISROW].[end])) and have searched many articles but no fruits.

If you give me a breakthrough, very helpful.

Steve

Like this:

SUM(
  SWITCH(
    [Choose A, B, or C],
    "A",
      SELECT(
        Table1[A],
        AND(
          ([Date] >= [_THISROW].[Start Date]),
          ([Date] <= [_THISROW].[End Date])
        )
      ),
    "B",
      SELECT(
        Table1[B],
        AND(
          ([Date] >= [_THISROW].[Start Date]),
          ([Date] <= [_THISROW].[End Date])
        )
      ),
    ...,
    LIST()
  )
)

See also:

View solution in original post

taufiq_qurohman

SUM(SELECT(table1[A],and([date]>=[_thisrow].[start_date],[date]<=[_thisrow].[end_date])))

Column A from Table 1 must be number or price type column

Harry_Ikeda

Thank you for your comment.

Yes, I have set the type as number.

Steve

Like this:

SUM(
  SWITCH(
    [Choose A, B, or C],
    "A",
      SELECT(
        Table1[A],
        AND(
          ([Date] >= [_THISROW].[Start Date]),
          ([Date] <= [_THISROW].[End Date])
        )
      ),
    "B",
      SELECT(
        Table1[B],
        AND(
          ([Date] >= [_THISROW].[Start Date]),
          ([Date] <= [_THISROW].[End Date])
        )
      ),
    ...,
    LIST()
  )
)