Use Var as a table index

[Aryan_S]

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]

Hi @Aryan_S 

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"

)

[Aryan_S]

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 · Accepted solution]

@Aryan_S 
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"
)