Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Use Var as a table index

Hi,

 

I want to use a variable to sum different columns of a table in a measure.
Instead of doing: 

SUM(Sales[01]) or SUM(Sales[02]) or SUM(Sales[03])

I want to do:
Var index = 01
SUM(Sales[index])

So that I can eventually do:
Var index = *some calculation*
SUM(Sales[index]) 
  • tamerj1's avatar
    tamerj1
    3 years ago

    Anonymous 
    This is a good solution given that your dealing with only 12 columns. In cases when you need to deal with un-pivoted data of 50 or 100 columns there is another solution which is way much more complex than yours and won't be feasible in your scenario. I would only simplify the formula as follows:

    Daily Budget =
SWITCH (
    MONTH ( TODAY () ),
    01, SUM ( NetSalesBudget[01] ),
    02, SUM ( NetSalesBudget[02] ),
    03, SUM ( NetSalesBudget[03] ),
    04, SUM ( NetSalesBudget[04] ),
    05, SUM ( NetSalesBudget[05] ),
    06, SUM ( NetSalesBudget[06] ),
    07, SUM ( NetSalesBudget[07] ),
    08, SUM ( NetSalesBudget[08] ),
    09, SUM ( NetSalesBudget[09] ),
    10, SUM ( NetSalesBudget[10] ),
    11, SUM ( NetSalesBudget[11] ),
    12, SUM ( NetSalesBudget[12] ),
    "Error"
)

     

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous 

    The easiest way is to unpivot the table to have one attribute column (equivalent to "index" in your example) and one value column. Not sure if this is a viable possibility in your situation as the formula would be as simple as 

    CALCULATE (

    SUM ( 'Table'[Value] ),

    'Table'[Attribute] = "some calculation"

    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello,

      Thanks for the response but I won't be able to change any of the underlying tables since they must remain as they currently are. I have found a solution:

      Daily Budget =
          VAR Index = FORMAT(TODAY(),"MM")
          Return SWITCH (
               TRUE(),
               Index = "01", SUM(NetSalesBudget[01]),
               Index = "02", SUM(NetSalesBudget[02]),
               Index = "03", SUM(NetSalesBudget[03]),
               Index = "04", SUM(NetSalesBudget[04]),
               Index = "05", SUM(NetSalesBudget[05]),
               Index = "06", SUM(NetSalesBudget[06]),
               Index = "07", SUM(NetSalesBudget[07]),
               Index = "08", SUM(NetSalesBudget[08]),
               Index = "09", SUM(NetSalesBudget[09]),
               Index = "10", SUM(NetSalesBudget[10]),
               Index = "11", SUM(NetSalesBudget[11]),
               Index = "12", SUM(NetSalesBudget[12]),
               "Error"
              )
       
      But it is long and messy so was just wondering if there was a cleaner way.
      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        Anonymous 
        This is a good solution given that your dealing with only 12 columns. In cases when you need to deal with un-pivoted data of 50 or 100 columns there is another solution which is way much more complex than yours and won't be feasible in your scenario. I would only simplify the formula as follows:

        Daily Budget =
SWITCH (
    MONTH ( TODAY () ),
    01, SUM ( NetSalesBudget[01] ),
    02, SUM ( NetSalesBudget[02] ),
    03, SUM ( NetSalesBudget[03] ),
    04, SUM ( NetSalesBudget[04] ),
    05, SUM ( NetSalesBudget[05] ),
    06, SUM ( NetSalesBudget[06] ),
    07, SUM ( NetSalesBudget[07] ),
    08, SUM ( NetSalesBudget[08] ),
    09, SUM ( NetSalesBudget[09] ),
    10, SUM ( NetSalesBudget[10] ),
    11, SUM ( NetSalesBudget[11] ),
    12, SUM ( NetSalesBudget[12] ),
    "Error"
)